• Article

CHECKPOINT (Transact-SQL)

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. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CHECKPOINT [ checkpoint_duration ]

Arguments

  • checkpoint_duration
    Specifies the requested amount of time, in seconds, for the checkpoint to complete. When checkpoint_duration is specified, the SQL Server Database Engine 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, SQL Server Database Engine automatically adjusts the checkpoint duration to minimize the performance impact on database applications.

    Note

    In SQL Server 2000, the timeframe for the checkpoint process was based on the sp_configure RECOVERY INTERVAL setting.

Remarks

For performance reasons, the Database Engine performs modifications to database pages in memory and does not write the page to disk after every change. However, periodically the Database Engine needs to perform a checkpoint to write these dirty pages to disk. Writing dirty pages to disk creates a known good point from which the Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash. For more information, see Checkpoints and the Active Portion of the Log.

Checkpoints can occur concurrently on any number of databases.

The Database Engine cannot recover from an interrupted checkpoint. If a checkpoint is interrupted and a recovery required, then the Database Engine must start recovery from a previous, successful checkpoint.

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. In addition, checkpoints occur automatically when either of the following conditions occur:

  • The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recoveryinterval 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.

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.

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

Factors Affecting the Duration of Checkpoint Operations

In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. To minimize the performance impact on other applications, SQL Server by default adjusts the frequency of writes that a checkpoint operation performs. SQL Server uses this strategy for automatic checkpoints and for any CHECKPOINT statement that does not specify a checkpoint_duration value. Decreasing the write frequency increases the time the checkpoint operation requires to complete.

You can use checkpoint_duration to request that the checkpoint operation complete within a specific amount of time. 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 SQL Server to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds causes SQL Server 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. SQL Server 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.

Permissions

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.