When we done operation on SQL SERVER that is not commited 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.When we stop the SQL Server it will take long time because Checkpoint
is also fired.
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.
Each time SQL Server 2005 checkpoint executes the calculated target establishes the max outstanding I/O level.
Depending on the type of checkpoint this value is adjusted as the I/Os complete.
SQL Server 2005 checkpoint adds duration tracking to the I/O requests. As each I/O completes the duration is used to
adjust a running I/O average. A running average is used to accommodate the 'immediate' state of the I/O path so
Checkpoint can react quickly to the current state and not the historical states.
When a checkpoint operation occurs, no matter how it's triggered (for instance through a manual CHECKPOINT, from a
database or differential backup, or automatically) the same set of operations occurs:
•All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were
read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.
• Before a page is written to disk, all log records up to and including the most recent log record describing a change to that
page are written to disk (yes, log records can be cached in memory too). This guarantees recovery can work and is called
write-ahead logging. Log records are written to the log sequentially, and log records from multiple transactions will be
interspersed in the log. The log cannot be selectively written to disk, so writing a dirty page to disk that only has a single log
record affecting it may mean writing many more previous log records to disk as well.
• Log records describing the checkpoint are generated.
• The LSN of the checkpoint is recorded in the database boot page in the dbi_checkptLSN field
• If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive
(called clearing or truncating the log - both of which are terrible misnomers, as nothing is either physically cleared or truncated).
Checkpoints are not really tracked in the transaction log - it just serves as a useful repository for information about which
transactions are active at the time of the checkpoint.
The LSN of the last checkpoint is recorded in the database boot page. This is where recovery starts, and if this page is
inaccessible, the database cannot be attached, opened, or processed in any way - partly because it's the boot page that
knows whether the database was cleanly shut down or not, and partly because it's the only place that records the LSN of the
last checkpoint record. You may say, well it's recorded in the transaction log too, but what if the log is corrupt in some way?
One area of confusion is that the checkpoint log records are overwritten by subsequent checkpoints. Absolutely not - once
written, a log record is NEVER updated or overwritten - it will only be overwritten when the log wraps and the VLFs are re-used.
This has led to further confusion about when checkpoint information is retrievable from the log, using commands such as fn_dblog.
How Check Points Work :: http://sqlskills.com/BLOGS/PAUL/post/How-do-checkpoints-work.aspx
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.
• Bringing an instance offline in a cluster.
Does the behavior change based on Database recovery models?
Checkpoint behavior changes based on the recovery model. There are 3 different recovery models inside SQL Server
Simple recovery model:- This uses minimum adiministrative overhead for the transaction log, the simple recovery model
risks significant workloss exposure if the database is damaged. Data is recoverable only to the most recent backup.
In simple recovery all transactions are truncating the log on each checkpoint.
Buld-logged recovery model:- Used for logging transactions while not filling up the log during bulk operations. This is used for
large scale operations such as buld import or index creation. Switching temporarily to the bull-logged recovery model increases
performance and reduces log space consumption. Log backups are still required if you want to be able to restore up to the point of failure.
Full recovery model:- The full recovery model gurantees the least risk of losing your work if a data file gets damaged.
In this model, SQL Server fully logs all operations. In this recovery model, you can recover to any Point-in-time and it is the
most recommended model for financial systems.