CHECKPOINT (T-SQL)

Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages that have been modified after they were read into the buffer cache, but the modifications have not yet been written to disk. If the current database is in log truncate mode, CHECKPOINT also truncates the inactive portion of the log. For more information about log truncation, Truncating the Transaction Log.

Syntax

CHECKPOINT

Remarks

The CHECKPOINT statement saves time in a subsequent recovery by creating a point at which all modifications to data and log pages 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.

Checkpoints also occur:

• When a database option is changed with sp_dboption. A checkpoint is executed in the database in which the option is changed.
• When a server is stopped, a checkpoint is executed in each database on the server. These methods of stopping SQL Server 7.0 checkpoint each database:
  • Using SQL Server Service Manager.
  • Using SQL Server Enterprise Manager.
  • Using the SHUTDOWN statement.
  • Using the Windows NT command net stop mssqlserver on the command prompt.
  • Using the services icon in the Windows NT control panel, selecting the mssqlserver service, and clicking the stop button.

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.

SQL Server 7.0 also automatically checkpoints any database where the lesser of these two conditions occur:

• The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.
• If the database is in log truncate mode and the log becomes 70% full.

A database is in log truncate mode when both these conditions are true:

• The trunc. log on chkpt. database option is set to TRUE.
• One of these events has occurred after the last BACKUP DATABASE statement referencing the database was executed:
  • A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.
  • A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.
  • An ALTER DATABASE statement that adds or deletes a file in the database is executed.

Permissions

Permission to use the CHECKPOINT statement defaults to members of the db_owner fixed database role, and is not transferable.

See Also