Checkpoint is a process that writes current in-memory dirty pages (modified pages) and transaction log records to physical disk. In SQL Server checkpoints are used to reduce the time required for recovery in the event of system failure. Checkpoint is regularly issued for each database. The following set of operations starts when checkpoint occurs:
- Log records from log buffer (including the last log record) are written to the disk.
- All dirty data file pages (pages that have been modified since the last checkpoint or since they were read from disk) are written into the data file from the buffer cache.
- Checkpoint LSN is recorded in the database boot page.
Types of Checkpoint
The Database Engine supports four types of checkpoints. While some of them are issued automatically in the background, other are triggered by user and some of them are triggered by certain system events.
Automatic Checkpoint
An automatic checkpoint is the most common type that is triggered by a background process. Server Configuration Option “Recovery Interval” is used by the SQL Server Database Engine to determine how often automatic checkpoints are issued on a given database. You can change it using sp_configure procedure. For example, execute the following command to set the recovery interval to 15 seconds:
EXEC [sp_configure] 'recovery interval', 15 GO RECONFIGURE GO
In the simple recovery model an automatic checkpoint truncates the unused section of the transaction log. As far as full or bulk-logged recovery model is concerned, the transaction log is not truncated by automatic checkpoint.
Indirect Checkpoint
A new type of checkpoint introduced in SQL Server 2012 is an Indirect checkpoint. Indirect checkpoint also runs in the background, but it meets user-specified target recovery time for a given database. By default TARGET_RECOVERY_TIME is 0, meaning that the database will use automatic checkpoints. If TARGET_RECOVERY_TIME is set to >0, it will override the Recovery Interval specified for the server and avoid automatic checkpoints for that database.
Use the following command to set the target recovery time for a database:
ALTER DATABASE database_name SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES}
Manual Checkpoint
Manual checkpoint runs like any other Transact-SQL command. It runs to completion by default. This type of checkpoint occurs in the current database only. It is also possible to set the time frame in which you want your checkpoint completed. Use the following command to issue manual checkpoint:
CHECKPOINT [ checkpoint_duration (in seconds) ]
Internal Checkpoint
The fourth type is Internal checkpoint that cannot be controlled by user. It starts following specific transactions, such as:
- Some database files have been modified (removed or added by T-SQL command ALTER DATABASE)
- Database backup is in progress
- Database snapshot is being created
- Shutdown operation occurred on all databases except when Shutdown is not clean (with NOWAIT)
- Recovery model has been changed from Full or Bulk-Logged to Simple
- Database log is 70% full (applies only to Simple recovery model)
- Minimally logged operation executed (applies only to Bulk-Logged recovery model)
