Go to SQL Server about checkpoint usage instructions

One of the most important commands in SQL Server is checkpoint, which is primarily to write data from the cache into an MDF file.

In fact, when we do insert, UPDATE, delete, the data is not directly written to the database corresponding to the MDF file, but written in the cache, which is a bit like an electric donkey, because too frequent write will make the life of the disk greatly reduced.

Can be seen from the visual. Data is written to the MDF data file only if checkpoint occurs.

The syntax for checkpoint is:
CHECKPOINT [ checkpoint_duration ], where checkpoint_duration is an integer value of type int and must be greater than 0, in seconds, representing SQL Server The database engine attempts to perform checkpoints during the duration of the request. If this argument is omitted, the SQL Server database engine automatically adjusts the checkpoint duration to minimize the impact on database application performance.

Events that cause checkpoint checkpoints:
1. Before the database backup, the database engine automatically performs checkpoints to include all changes to the database pages in the backup.

2. The active portion of the log exceeds the size that the server can recover within the time specified in the recovery interval server configuration option.

3. The log is 70% full, and the database is in log truncation mode.

The database is in log truncation mode when the following conditions are TRUE: The database is using the simple recovery model, and one of the following events occurs after the previous BACKUP database statement that references the databases is executed:

A WRITETEXT statement that performs a minimum logging bulk copy operation or one of the most small log records in the database.

Executes an ALTER database statement that adds or deletes a file in the database.

4. Stopping the server also issues a checkpoint command in each database on the server. The following methods of stopping SQL Server will perform checkpoints for each database:

Use SQL Server Configuration Manager.

Use SQL Server Management Studio.

Use the SHUTDOWN statement.

For reprint, please specify the original self-csdn tjvictor column of this article: Http://blog.csdn.net/tjvictor

Go to SQL Server about checkpoint usage instructions