Microsoft® SQL Server 2008 R2 Unleashed

The Checkpoint Process

During recovery, SQL Server examines the transaction log for each database and verifies whether the changes reflected in the log are also reflected in the database. In addition, it examines the log to determine whether any data changes that were written to the data were caused by a transaction that didn’t complete before the system failure.

As discussed earlier, a COMMIT writes the log records for a transaction to the transaction log (see Figure 31.1). Dirty data pages are written out either by the Lazy Writer or checkpoint process. The Lazy Writer process runs periodically to check whether the number of free buffers has fallen below a certain threshold, reclaims any unused pages, and writes out any dirty pages that haven’t ...