Checkpoints minimize the portion of the log that must be processed during a full recovery of a database. During a full recovery, two types of actions must be performed:
Checkpoints flush dirty data pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery. To reduce the effect of a checkpoint while a system is running, Microsoft® SQL Server™ version 7.0 does not flush every dirty page in the buffer cache on a checkpoint. Instead, it flushes every dirty page that was dirty at the time of the last checkpoint and which still has not been flushed. Because a dirty page can wait for an extra checkpoint before being forced to disk, the SQL Server 7.0 lazywriter process gains a greater opportunity to flush dirty pages at times of low I/O activity. Few pages have to be written immediately at checkpoints. It also means that SQL Server 7.0 starts the roll forward phase of recovery at the second-to-last checkpoint in the log, not the last checkpoint as in earlier versions of SQL Server.
A SQL Server 7.0 checkpoint performs these processes in the current database:
The portion of the log file from the MinLSN to the end of the log is called the active portion of the log. This is the portion of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log truncation must be done from the parts of the log before the MinLSN.
This is a simplified version of the end of a transaction log with two active transactions. The checkpoint records have been compacted to a single record.
LSN 148 is the last record in the transaction log. At the time the checkpoint recorded at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. This makes LSN 142, the begin transaction record for Tran 2, the MinLSN.
Checkpoints occur:
Either of these methods checkpoint each database on the server.
SQL Server 7.0 always generates automatic checkpoints, regardless of the setting of the trunc. log on chkpt. database option. The only effects of the option on the automatic checkpoints in SQL Server 7.0 is whether the checkpoints truncate the inactive portion of the log, and how the interval between automatic checkpoints is determined.
The interval between automatic checkpoints is based on the number of records in the log, not time. The time interval between automatic checkpoints can be highly variable. The time interval between automatic checkpoints is long if few modifications are made in the database. Automatic checkpoints occur frequently if a lot of data is modified.
The interval between automatic checkpoints is calculated from the recovery interval server configuration option. This option specifies the maximum time SQL Server should use to recover a database during a system restart. SQL Server estimates how many log records it can process in the recovery interval during a recovery operation. The interval between automatic checkpoints also depends on whether the database is in log truncate mode. For more information about log truncate mode, see Truncating the Transaction Log.
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:
| CHECKPOINT | Transaction Recovery |
| Backup/Restore Architecture | Freeing and Writing Buffer Pages |