Microsoft® SQL Server 2012 Unleashed

Manual Checkpoints

In addition to automatic checkpoints, a checkpoint can be explicitly initiated by members of the sysadmin fixed server role or the db_owner or db_backupoperator fixed database roles. The syntax for the CHECKPOINT command is as follows:

Click here to view code image

CHECKPOINT [ checkpoint_duration ]

To minimize the performance impact on other applications, SQL Server 2012 by default adjusts the frequency of the writes that a checkpoint operation performs. SQL Server uses this strategy for automatic checkpoints and for any CHECKPOINT statement that does not specify the checkpoint_duration value.

You can use the checkpoint_duration option to request the amount of time, in seconds, for the checkpoint to complete. When checkpoint_duration ...