When we have the done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint is the point which alerts SQL Server to save all the data to the main database if no Checkpoint is there then log files get full. We can use Checkpoint command to commit all data in the SQL SERVER.
Or we can say that Checkpoints Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
Syntax for using Checkpoint
CHECKPOINT [ checkpoint_duration ]
where
checkpoint_duration: Specifies the requested amount of time, in seconds, for the checkpoint to complete. When checkpoint duration is specified, the SQL Server Database Engine attempts to perform the checkpoint within the requested duration. The checkpoint_duration must be an expression of type int and must be greater than zero. When this parameter is omitted, SQL Server Database Engine automatically adjusts the checkpoint duration to minimize the performance impact on database applications.
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 occurs:
•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.
Anonymous User
14-Mar-2019Thank You for sharing details.
Manish Kumar
06-Jul-2017It was really helpful to read this post.