SQL SERVER transaction log

When talking about the transaction log, you have to talk about the Checkpoint, or CKP for short. The transaction log and the Checkpoint both exist for the rapid recovery of the database. We need to know what the checkpoint is and what it has to do with the transaction log.

What does CKP do?

In summary, data changes are not directly written to the mdf/ndf data file, but to the buffer pool. When CKP is triggered, the check point will refresh the "all" dirty data page from the buffer pool of the current database to the data file. The so-called dirty data is the changed data pages, which are not written to the disk in the memory. All dirty data includes submitted data changes and uncommitted data changes. Refresh to a data file refers to writing the image copy after the data is changed to the data file. The changes to the data will retain a previous image and a later image, like modifying an article, save the original text as the original image, and then use the modified article as the later image. At the same time, CKP will flush cached log to the log file and click the open link. I prefer to regard CKP as an alarm clock. We can schedule it according to a rule and remind the SQL component data writer/log writer to work.

In most cases, it is determined by CKP when data changes are written to the data file.

When is CKP triggered?

Backup database (full backup, differential backup, log backup)

Checkpoint command

Close Database

Alter database Command

Automatic checkpoint triggering (SQL triggers a checkpoint based on the time limit recommended by the recovery interval server configuration option or when the log is 70% full)

Indirect checkpoint (new features in SQL 2012, click here for details)

Differences between CKP, Lazywriter, and Eagerwriter

The processes of these three SQL statements will write dirty data into the data file, but their purpose and trigger conditions are completely different.

CKP is used to quickly recover instances after SQL intance is stopped (such as power failure. It is a time point mark, marking the starting point of database recovery. At this starting point, all the dirty data copies are written to the disk (it does not release the memory space), thus saving time in future recovery.

Lazy writer makes better use of the buffer pool. It regularly checks the data cache, writes unused dirty pages to the disk according to the LRU algorithm, and clears dirty pages to release memory space.

The Eager writer exists for batch inserts such as BCP. This process writes dirty data pages associated with non-log operations (such as large-capacity insertion and selection of insertion. This process allows you to create and write new pages in parallel. That is to say, the call operation does not have to wait until the entire operation is completed, and the page can be written to the disk (Abstract: msdn click to open the link ). This ensures that the Buffer Pool has sufficient space for batch operations.

What is the relationship between CKP and transaction logs?

It is not so much the relationship with transaction logs as the relationship with the transaction Log serial Number (LSN). I will elaborate on LSN in my later blog.

The checkpoint trigger has a start time point, which is recorded in the transaction log and obtained a CKP start time point LSN. After dirty data is written, CKP also records an LSN at the end time in the log file. The checkpoint also records a minimum LSN (minLSN). The minLSN must exist in the checkpoint record to be successfully rolled back (roll back/undo ), minLSN indicates the farthest time point for SQL Server rollback. As for what minLSN is, how does SQL define minLSN? How does SQL server use it? I will elaborate on my blog later) Remember That CKP will record this minLSN, which is very important to undo.