Tutorial :SQL Server checkpoints



Question:

can anyone explain when SQL Server issues a checkpoint?


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »