CHECKPOINT

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.

Events That Cause Checkpoints
=============================
>> Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

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

>> 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:

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

>> Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

Using SQL Server Configuration Manager.

Using SQL Server Management Studio.

Using the SHUTDOWN statement.

Using the net stop mssqlserver command in a command-prompt window.

Using Services in Control Panel, selecting mssqlserver, and clicking Stop.

Bringing an instance offline in a cluster..

> A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.

> A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.

> An ALTER DATABASE statement that adds or deletes a file in the database is executed.

Note

The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.