Checkpoint Operation

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.

Checkpoint

CHECKPOINT : As per MSDN/BOL , A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log because For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every changes made. Checkpoint is the SQL engine system process that writes all dirty pages to disk for the current database. The benefit of the Checkpoint process is to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

When CHECKPOINT happen?

• 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 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. Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
  
• Stopping the server using any of the following methods, they it cause a checkpoint.
  
  • Using Shutdown statement,
    
  • Stopping SQL Server service through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
    
  • When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
    
• When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
  
• When the transaction log is 70% full and the database is in truncation mode.
  
• The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.
  
• 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.
  
• Long-running uncommitted transactions increase recovery time for all types of checkpoints.
  

The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

We can monitor checkpoint I/O activity using Performance Monitor by looking at the “Checkpoint pages/sec” counter in the SQL Server:Buffer Manager object.