SQL Server checkpoints

问题:

can anyone explain when SQL Server issues a checkpoint?

回答1:

from: http://msdn.microsoft.com/en-us/library/ms188748.asp

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 recovery interval 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.