What is Checkpoint in SQL server? What is the use of Checkpoint?

In Microsoft SQL Server, checkpoint is a very important process which helps to write all modifications made in the database from buffer cache to disk.

This process helps to reduce the possibility of data loss in case of any disaster or system failure.

Lazy Writer is also available in SQL Server to write modified data from buffer cache to disk if everything is normal.

But this approach may not work properly in case of system failure or unexpected shutdown.

To overcome this situation, SQL server has Checkpoint feature which transfers all modified pages to disk.

Below are the conditions in which a checkpoint occurs:-

If the database is attached.

If the ALTER DATABASE statement is executed with the MODIFY FILE option.

If the database is shut down.

If the database is detached.

If the transaction log becomes full.

If the recovery model  of database is set to SIMPLE T-Log file reaches a specified size. 

In SQL Server, checkpoints can occur automatically based on certain parameters which are configured:

a) Recovery Interval - This parameter defines the time gap between two occurrence of Checkpoint. By default, the value of this parameter is set as 0 which means it is in disabled mode. We can use below statement to set recovery interval :

EXEC sp_configure 'recovery interval', 'seconds'

b) Recovery Interval In Kilobytes - This parameter defines amount of data that must be modified before the automatic checkpoint is triggered. Default value of this parameter is 1024 KB. It means the automatic checkpoint will occur after 1 MB of data has been modified.

c) Log Flush Frequency - This parameter defines the frequency to flush the transaction log to disk. Default value of this parameter is every 60 seconds.

Database Engine supports four types of checkpoints: 

1) Automatic:- Background process to write modified data pages from buffer cache to disk. Automatic checkpoint works based on the number of pending writes and if the Database Engine observes write latency is more than 50 ms(milliseconds).

2) Indirect:- Background process to write modified data pages from buffer cache to disk. From SQL Server 2016, default value of indirect checkpoint is 1 minute. Default value of Indirect Checkpoint in previous versions of SQL Server is 0. It means, the database will use automatic checkpoints. 

ALTER DATABASE [DBName] SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

3) Manual:- when we execute a Transact-SQL CHECKPOINT command, it runs & write modified data pages from buffer cache to disk. 

CHECKPOINT [ checkpoint_duration ]

The checkpoint_duration parameter specifies the amount of time (in seconds) for the checkpoint.

4) Internal:- Internal checkpoint is used by various server operations like backup and database-snapshot creation etc. 

FAQs:-

Q. SQL Server what is checkpoint? / What is a checkpoint in SQL Server?

A. Just refer first line of this article to know more about the checkpoint. 

Q. What does checkpoint do in SQL Server? / Use of checkpoint in SQL Server 2016?

A. Just refer first line of this article to know more about the checkpoint.

Q. How to run a checkpoint in SQL Server? / How to issue checkpoint in SQL Server?

A. We can run checkpoint manually like below:

Use [database]

go

CHECKPOINT

Q. When checkpoint occurs in SQL Server?

A. Just refer "Database Engine supports four types of checkpoints" section in this article to get more details.