WikiDBA

Checkpoints operation flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During a full recovery, the following types of actions are performed:

• The log records of modifications not flushed to disk before the system stopped are rolled forward.
  
• All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.
  
• A checkpoint performs the following processes in the database:
  
• Writes a record to the log file, marking the start of the checkpoint.
  
• Stores information recorded for the checkpoint in a chain of checkpoint log records.
  
• One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN ,The Minimum Recovery LSN is the minimum of the:
  
  • LSN of the start of the checkpoint.
    
  • LSN of the start of the oldest active transaction.
    

The checkpoint records also contain a list of all the active transactions that have modified the database.

• If the database uses the simple recovery model, marks for reuse the space that precedes the Minimum Recovery LSN.
  
• Writes all dirty log and data pages to disk.
  
• Writes a record marking the end of the checkpoint to the log file.
  
• Writes the LSN of the start of this chain to the database boot page.
  

Activities That Cause a Checkpoint

Checkpoints occur in the following situations:

• A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
  
• A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  
• Database files have been added or removed by using ALTER DATABASE.
  
• An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.
  
• An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  
• A database backup is taken.
  
• An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.