| title | CHECKPOINT (Transact-SQL) | Microsoft Docs | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ms.custom | ||||||||||||
| ms.date | 07/27/2017 | |||||||||||
| ms.prod | sql-non-specified | |||||||||||
| ms.reviewer | ||||||||||||
| ms.suite | ||||||||||||
| ms.technology |
|
|||||||||||
| ms.tgt_pltfrm | ||||||||||||
| ms.topic | language-reference | |||||||||||
| f1_keywords |
|
|||||||||||
| dev_langs |
|
|||||||||||
| helpviewer_keywords |
|
|||||||||||
| ms.assetid | ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406 | |||||||||||
| caps.latest.revision | 59 | |||||||||||
| author | JennieHubbard | |||||||||||
| ms.author | jhubbard | |||||||||||
| manager | jhubbard | |||||||||||
| ms.workload | On Demand |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Generates a manual checkpoint in the [!INCLUDEssNoVersion] database to which you are currently connected.
Note
For information about different types of database checkpoints and checkpoint operation in general, see Database Checkpoints (SQL Server).
Transact-SQL Syntax Conventions
CHECKPOINT [ checkpoint_duration ]
checkpoint_duration
Specifies the requested amount of time, in seconds, for the manual checkpoint to complete. When checkpoint_duration is specified, the [!INCLUDEssDEnoversion] attempts to perform the checkpoint within the requested duration. The checkpoint_duration must be an expression of type int and must be greater than zero. When this parameter is omitted, the [!INCLUDEssDE] adjusts the checkpoint duration to minimize the performance impact on database applications. checkpoint_duration is an advanced option.
In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. By default, to minimize the performance impact on other applications, [!INCLUDEssNoVersion] adjusts the frequency of writes that a checkpoint operation performs. Decreasing the write frequency increases the time the checkpoint operation requires to complete. [!INCLUDEssNoVersion] uses this strategy for a manual checkpoint unless a checkpoint_duration value is specified in the CHECKPOINT command.
The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified. For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes [!INCLUDEssNoVersion] to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds would cause [!INCLUDEssNoVersion] to assign fewer resources than would be assigned by default. In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. [!INCLUDEssNoVersion] always completes a checkpoint if possible, and the CHECKPOINT statement returns immediately when a checkpoint completes. Therefore, in some cases, a checkpoint may complete sooner than the specified duration or may run longer than the specified duration.
CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.
ALTER DATABASE (Transact-SQL)
Database Checkpoints (SQL Server)
Configure the recovery interval Server Configuration Option
SHUTDOWN (Transact-SQL)