CHECKPOINT in SQL Server

 

What is CHECKPOINT?

As per BOL definition “ Writes all dirty pages for the current database to disk.
Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.
Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk”

This minimizes the active portion of the log that must be processed during a full recovery of a database.

What are the Events that causes CHECKPOINT?

1. Before Database Backup

Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

2. Active Log exceeds recovery interval

The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

3. The log becomes 70 percent full, and the database is in log-truncate mode.

A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

• BULK_LOG Recovery: A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.
• An ALTER DATABASE statement is executed that adds or deletes a file in the database

4. Stopping a SQL Server issues a checkpoint in each database on the server

5. CHECKPOINT:
A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

6. Database Shutdown
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.