What is Checkpoint ? it usage in SQL Server

 What is Checkpoint ? it usage in SQL Server:

Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.
Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

In SQL Server 2012 there are four types of Checkpoints:
Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.
Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.
Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.
Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:
1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)
2. If the recovery model gets changed from Full\Bulk-logged to Simple.
3. While taking Backup of the Database.
4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
5. Alter Database command to add or remove a data\log file also initiates a checkpoint.
6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
7. DB Snapshot creation.

Checkpoint

 [Total: 5    Average: 4/5]

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:

1. Log records from log buffer (including the last log record) are written to the disk.
2. 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.
3. 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)