CHECKPOINT

Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk. For more information about log truncation, see 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.

Checkpoints also occur:

• When a database option is changed with ALTER DATABASE. 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 Microsoft® SQL Server™ 2000 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 2000 also automatically checkpoints any database where the lesser of these 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 percent full.

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

• The database is using the simple recovery model.
  
  
• 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

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.

See Also

ALTER DATABASE

Checkpoints and the Active Portion of the Log

recovery interval Option

Setting Database Options

SHUTDOWN