About CHECKPOINT

The checkpoint mechanism is an automatic means for guaranteeing that completed transactions are regularly written from SQL Server's own disk cache to the database device. A checkpoint writes all dirty pages¾cached pages that have been modified since the last checkpoint¾to the database device.

There are two types of checkpoints:

• Checkpoints that are executed automatically by SQL Server.
• Checkpoints that are forced by database owners or the SA with the CHECKPOINT statement.

Forcing dirty pages onto the database device means that all completed transactions are written out. By calling all completed transactions to be written out, the checkpoint shortens the time it takes to recover, since the database pages are current and there are no transactions that need to be rolled forward. A typical checkpoint takes only a second or two.

The automatic checkpoint interval is calculated by SQL Server on the basis of system activity and the recovery interval configuration option, which specifies the maximum acceptable recovery time. The checkpoint (actually, the CHECKPOINT process) also performs a few other tasks, including truncating the log if the database option has been set.

Database owners can force a checkpoint at any time by executing the CHECKPOINT statement. Permission to execute the CHECKPOINT statement defaults to the database owner and cannot be transferred to other users.

The CHECKPOINT statement applies to the current database. Like the automatic checkpoint, it forces all dirty pages in the database to be written to disk. The CHECKPOINT statement is used in the following ways:

• As a precautionary measure in special circumstances¾for example, just before a planned restart so that the SQL Server recovery mechanisms won't have anything to recover.
• To cause a change in database options to take effect after the database options have been reset.
• To make a ROLLBACK TRANSACTION statement take effect when canceling a user-defined transaction in which a database option was changed.

For more information about CHECKPOINT, see the Microsoft SQL Server Transact-SQL Reference.