Forces all dirty pages (those that have been updated since the last checkpoint) in the current database to be written to disk. Checkpoints caused by the CHECKPOINT statement supplement automatic checkpoints, which occur at intervals calculated by SQL Server on the basis of the configurable value for maximum acceptable recovery time.
CHECKPOINT
The CHECKPOINT statement saves time in recovery by identifying a point at which all completed transactions are guaranteed to have been written to disk.
A typical checkpoint takes 1 second to complete, although this figure varies depending on the amount of activity on SQL Server and the size of the data cache.
The automatic checkpoint interval is calculated by SQL Server on the basis of system activity and the recovery interval value in the syscurconfigs system table. The recovery interval determines checkpoint frequency by specifying the amount of time it should take the system to recover. Reset this value by executing the sp_configure system stored procedure and the RECONFIGURE statement. For details, see the Microsoft SQL Server Administrator's Companion.
Although automatic recovery will always recover rows after a power failure, use CHECKPOINT as a precautionary measure and to speed up recovery after power failures. For example, if the automatic checkpoint is infrequent because the recovery interval is set high, use the CHECKPOINT statement to force the dirty pages out of the data cache and onto the actual disk pages.
Note In addition to occurring automatically at the recovery interval, a checkpoint will also occur at two other times: After you change a database option with the sp_dboption system stored procedure, a checkpoint will automatically occur within the database where the option was changed. Using the SHUTDOWN statement or stopping the server with the SQL Service Manager also forces a checkpoint in every database.
Permission to use the CHECKPOINT statement defaults to the database owner. It cannot be transferred.
| RECONFIGURE | sp_dboption |
| SHUTDOWN |