SQL SERVER Transaction Log

When talking about the transaction log, we have to talk about the checkpoint Checkpoint, referred to as CKP. Both transaction logs and checkpoints 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's CKP for?

It is well known that the data changes are not written directly into the MDF/NDF data file, but are written into the buffer pool; When the CKP is triggered, the checkpoint "all" dirty data page is flushed from the buffer pool in the current database to the data file. The so-called dirty data, which is the changed page, is not written to disk in memory. All dirty data includes data changes that have been committed and uncommitted data changes. Refreshing to a data file refers to the post mirror copy of the data changes written to the data file, changes to the data will retain a front mirror and a back mirror, like we modify the article, first save the original as a mirror image, and then the modified article as a mirror. At the same time CKP will also flush cached log to the logfile to click on the open link. I prefer to see CKP as an alarm clock, we can schedule it according to a rule, and then remind SQL of the parts data writer/log writer go to work.

In summary, in most cases, when data changes are written to the data file, it is up to CKP to decide.

When was CKP triggered?

Backup Database (full, differential, log backup)

Checkpoint command

Close Database

Alter Database command

Automatic checkpoint firing (SQL based on recovery interval Server configuration option recommended time limit trigger checkpoint or log full 70% trigger checkpoint)

Indirect checkpoint (SQL 2012 new features, details involved click on open link)

The difference between CKP, LazyWriter, Eagerwriter

It is always known that the three-SQL process will write dirty data to the data file, but their purpose and trigger conditions are completely different

The purpose of CKP is to quickly instance recovery after the SQL Intance is stopped (for example, a power outage). It is a point-in-time marker that marks the starting point for database recovery. At this starting point, all dirty data copy is written to the disk (it does not free up memory space), which saves time in the future recovery process.

Lazy writer is to make better use of the buffer pool. It periodically checks the data cache and, according to the LRU algorithm, writes long unused dirty page to disk, clearing the dirty page to free up memory space.

The Eager writer exists for bulk inserts such as BCP. The process writes a dirty data page that is associated with operations that are not logged, such as bulk inserts and selection inserts. This process allows the creation and writing of new pages in parallel. That is, the calling operation does not have to wait for the entire operation to complete, and the page is written to disk (click on MSDN to open the link). This ensures that the buffer pool has enough space to complete the bulk operation.

What does CKP have to do with the transaction log?

Rather than the relationship with the transaction log, it is related to the transaction log sequence number (log Sequence numbers, LSN), which will be described in my blog later.

The checkpoint trigger has a starting point at which the Point-in-time is recorded in the transaction log to obtain a CKP starting point LSN. When the dirty data is written, CKP also records an end point LSN in the log file. The checkpoint also records a minimum recovery LSN (MinLSN), MinLSN must exist in the checkpoint record to successfully roll back (roll Back/undo), and MinLSN tells SQL Server the furthest point in time that can be rolled back. As for what MINLSN is, how SQL defines how Minlsn,sql server uses it, and after my blog is elaborated here, just remember that CKP will record this minlsn and it's very important for undo.