Database Reference
In-Depth Information
A write-ahead logging mechanism guarantees that dirty data pages are never saved into the data files until the
corresponding log records are hardened in the transaction log. The opposite, however, is not true. The CHECKPOINT
process is asynchronous, and there is a delay in between when log records are hardened and when pages in the data
files are updated. Moreover, CHECKPOINT does not analyze if the transactions that modified data pages were actually
committed. Therefore, some pages in the data files reflect changes from uncommitted transactions.
The goal of the recovery process is to make the database transactionally consistent. SQL Server analyzes the
transaction log, making sure that all changes from committed transactions are saved into the data files and all changes
from uncommitted transactions are rolled back.
The recovery process consists of three different phases:
1.
During the
analysis
phase, SQL Server locates the last CHECKPOINT operation in the
log file, which is the last time dirty pages were saved into the data file. SQL Server builds
the list of pages that were modified after CHECKPOINT as well as the list of uncommitted
transactions at the time when SQL Server stopped.
2.
During the
redo
phase, SQL Server analyzes the transaction log from the initial LSN of the
oldest active transaction at the moment of the crash, which is stored in the database boot
page, and applies the changes to the data. Even though some of the changes could already
be saved to the data files, SQL Server acquires locks on the modified rows similar to a
regular workload. At the end of redo phase, the database is then in the state that it was at
the time when SQL Server shut down unexpectedly.
3.
Finally, during the
undo
phase, SQL Server rolls back all active, uncommitted
transactions.
Figure
29-7
shows an example of a recovery scenario for the database. SQL Server will redo and commit
transactions T2 and T3 and roll back transaction T4.
Figure 29-7.
Database recovery
The recovery process uses a single thread per database. The Enterprise edition of SQL Server supports
fast
recovery,
which makes the database available to users after the redo stage.
