Page 58 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Rolling a Restored Database, File, Filegroup, or Page Forward to the Point of Failure

After a hardware loss or disk failure affecting the database files, you can restore the database to
the point of failure. You first restore the last full database backup and the last differential
database backup, and then restore the subsequent sequence of the transaction log backups to the
point of failure. As you restore each log backup, the Database Engine reapplies all the
modifications recorded in the log to roll forward all the transactions. When the last log backup
is restored, the Database Engine then uses the log information to roll back all transactions that
were not complete at that point.

Supporting Transactional Replication

The Log Reader Agent monitors the transaction log of each database configured for transactional
replication and copies the transactions marked for replication from the transaction log into the
distribution database.

Supporting Standby-Server Solutions

The standby-server solutions, database mirroring, and log shipping, rely heavily on the
transaction log. In a log shipping scenario, the primary server sends the active transaction log of
the primary database to one or more destinations. Each secondary server restores the log to its
local secondary database.
In a database mirroring scenario, every update to a database, the principal database, is
immediately reproduced in a separate, full copy of the database, the mirror database. The
principal server instance sends each log record immediately to the mirror server instance which
applies the incoming log records to the mirror database, continually rolling it forward.

4.4.2 Transaction Log Characteristics

Following are the characteristics of the SQL Server Database Engine transaction log:
The transaction log is implemented as a separate file or set of files in the database. The log
cache is managed separately from the buffer cache for data pages, which results in simple,
fast, and robust code within the Database Engine.

The format of log records and pages is not constrained to follow the format of data pages.
The transaction log can be implemented in several files. The files can be defined to expand
automatically by setting the FILEGROWTH value for the log. This reduces the potential of
running out of space in the transaction log, while at the same time reducing administrative
overhead.

The mechanism to reuse the space within the log files is quick and has minimal effect on
transaction throughput.

Self Assessment

Fill in the blanks:
7. ………………… are named collections of files and are used to help with data placement
and administrative tasks such as backup and restore operations.
8. ………………… is the name used to refer to the physical file in all Transact-SQL statements.
9. ………………… is the name of the physical file including the directory path.

52 LOVELY PROFESSIONAL UNIVERSITY