At Checkpoint SQL Server writes all dirty pages for the current database to disk. (all pages that have changed in memory since they were read from disk or since the last checkpoint). This way it saves time in case of a 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.
Reference : http://www.sqlskills.com/blogs/paul/how-do-checkpoints-work-and-what-gets-logged/
