Checkpoints and the Active Portion of the Log
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:
- The log may contain records of modifications not flushed to disk before the system stopped. These modifications must be rolled forward.
- All the modifications associated with incomplete transactions (transactions for which there is no COMMIT or ROLLBACK log record) must be rolled back.
Checkpoints flush dirty data and log pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery.
A SQL Server 2000 checkpoint performs these processes in the current database:
- Writes to the log file a record marking the start of the checkpoint.
- Stores information recorded for the checkpoint in a chain of checkpoint log records. The LSN of the start of this chain is written to the database boot page.
- One piece of information recorded in the checkpoint records is the LSN of the first log image that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN) and is the minimum of:
- The LSN of the start of the checkpoint.
- The LSN of the start of the oldest active transaction.
- The LSN of the start of the oldest replication transaction that has not yet replicated to all subscribers.
- The LSN of the start of the checkpoint.
- Another piece of information recorded in the checkpoint records is a list of all outstanding, active transactions.
- Deletes all log records before the new MinLSN, if the database is using the simple recovery model.
- Writes to disk all dirty log and data pages.
- Writes to the log file a record marking the end of the checkpoint.
The portion of the log file from the MinLSN to the last-written log record 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. 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:
- When a CHECKPOINT statement is executed. The current database for the connection is checkpointed.
- When ALTER DATABASE is used to change a database option. ALTER DATABASE checkpoints the database when database options are changed.
- When an instance of SQL Server is stopped by:
- Executing a SHUTDOWN statement.
- Using the SQL Server Service Control Manager to stop the service running an instance of the database engine.
Either of these methods checkpoints each database in the instance of SQL Server.
- Executing a SHUTDOWN statement.
- When an instance SQL Server periodically generates automatic checkpoints in each database to reduce the amount of time the instance would take to recover the database.
Automatic Checkpoints
SQL Server 2000 always generates automatic checkpoints. 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 or not the database is using the simple recovery model.
- If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
- If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
- The log becomes 70 percent full.
- The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
- The log becomes 70 percent full.
Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models. For more information, see Truncating the Transaction Log.
Long-Running Transactions
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:
- If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.
- The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.
Replication Transactions
The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.