When we 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 are
the point which alert Sql Server to save all the data to main database if no
Check point 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 ]
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
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.