Database checkpoint in SQL Server

Based on performance considerations, the database engine performs changes to the database data pages (pages) in memory (buffer cache) and does not write the modified page back to disk after each modification. More accurately, the database engine periodically generates a checkpoint on each database. Checkpoint will write the current in-memory modified pages (dirty pages) and transaction log information from memory to disk, and log information about tranaction log.

The database engine supports the following types of checkpoint:

• Automatic-automatically generates checkpoint periodically in the background to meet the recovery interval Standard time limit required by the server configuration. How much automatic checkpoint executes is based on the number of outstanding write operations and whether the database engine detects that the write delay is more than 20 milliseconds.
• Indirect-executes in the background to allow a database to reach the user-specified target revovery time standard. The default target Revovery time is 0, which causes the automatic checkpoint setting to take effect on the database. If you previously configured Target_recovery_time to be greater than 0 with ALTER DATABASE, the value greater than 0 would take effect instead of having the value set for server instance to work.
• Manual-The checkpoint of manual is generated whenever you execute the CHECKPOINT command in Transact-SQL. Manual checkpoint will rise smaller in the database you are currently connected to. By default, manual checkpoint will execute until the end. The working mechanism of trrottling is the same as that of automatic checkpoint.
• Internal-can be triggered by a number of server operations, such as a backup, a snapshot of the database (snapshot), to ensure that the disk's image is consistent with the current log status.

Example:

--Enable indirect checkpoint1 SECONDSgo--Enable automatic checkpoint  0 SECONDS go  

Resources

======================

Database checkpoints (SQL Server)

Http://msdn.microsoft.com/en-us/library/ms189573.aspx

Database checkpoint in SQL Server