Checkpoint – Concept and configurations we need to know

Checkpoint is one of the core functions SQL Server engine. Checkpoint flushes dirty data pages from the buffer cache of the current database to disk. This in-turn ensures that database log has manageable amount of data if the database goes in-to recovery procedure. On a high level, checkpoint on a database marks the log file and then flushed the inactive part of logs (before MiniLsn) from buffer memory to disk.  Also if the database uses the simple recovery model, during the process of its execution, checkpoint marks the part of transaction log (which is already flushed to disk) which can be reused.

Implementation of Checkpoint is completely automatic and typically administrator does not have to worry about customizing it.  Automatic execution of checkpoint largely depends on recovery model and usages of the database.

If the database is using either full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

If the database is using simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of below values:

a. The log becomes 70 percent full.
b. The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

So generation of checkpoint heavily depends on the usages of the database too. For an OLTP database with frequent change, a frequent checkpoint issue is common. On the other hand, a database which observes minimum change can experience very long interval between 2 checkpoints.

Also checkpoints occur in the following situations:

• When 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.
• 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.

As execution of checkpoint on a database has a direct impact of transaction log truncation capability, I personally recommend not altering default settings of SQL Server checkpoint unless it is absolutely necessary.

However you can request SQL Server to issue a checkpoint on a database after a certain interval using below syntax:

use [database name]
CHECKPOINT [interval in minutes]

Example (This code will advice SQL Server to issue checkpoint in every 5 minutes interval):

use test
CHECKPOINT 5

From SQL Server level, you can control the behaviour of checkpoint executing by modifying “Recovery interval property” using below syntax:

EXEC sys.sp_configure N'recovery interval (min)', [interval in minutes]'

GO

RECONFIGURE WITH OVERRIDE

GO

It is recommended to leave this value to it’s default (0) which allows SQL Server to take best course of action for each database to issue checkpoint as discussed above.