can anyone explain when SQL Server issues a checkpoint?
Answer
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.