Log Reuse Waits Explained: CHECKPOINT

Introduction

There are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: CHECKPOINT

Buffer Pool

The SQL Server query engine never directly interacts with the data files on disk. Instead it requests the data pages it needs to process a query from the buffer pool manager. The buffer pool manager checks if that page is already in the buffer pool and if so returns a memory pointer back to the query engine.

If the page however is not yet in the buffer pool, the buffer pool manager requests it from the storage engine. The storage engine then proceeds to locate the page on disk and once found reads it into the buffer pool. After the pages is completely loaded, the buffer pool manager wakes the query engine back up and presents the new page location.

Dirty Pages

If a data changing query is executed, the changes are applied to the pages in memory and not directly written to disk. If the page that needs to be changed is not currently in memory, the same process to load it that was described above is executed first. Once the page is in memory, the changes are applied and the page is marked as having changed or as "dirty".

Checkpoint

In regular intervals a background process runs to write those dirty pages back to disk. Once all currently dirty pages are saved safely on disk, a special mark is written to the log. This allows SQL Server to process recovery after a crash starting at the most recent mark in the log, as all prior changes have already been written to disk.

This special mark is called "Checkpoint". The background process runs automatically often enough so that the estimated crash recovery time is lower than the recovery interval server setting. This setting is by default set to 0 which is interpreted by SQL Server as one minute. That means that on a fairly busy system you will have a checkpoint about every minute, but on a system that is not well utilized, the time between checkpoints can be significantly larger.

Besides of the scheduled automatic checkpoint, you can trigger a manual checkpoint by executing the CHECKPOINT command.

The checkpoint allows SQL Server to start the recovery process at that point in the log. All log records prior to the checkpoint can be ignored. All log records that were written after the checkpoint, have to be processed. This includes records of transactions that started before the checkpoint and were committed or rolled back after the checkpoint.

The Checkpoint Wait

This means, that a log record of a committed transaction is still required for crash recovery until the next checkpoint has been executed. In this situation the virtual log files containing these log records cannot be reused and SQL Server reports CHECKPOINT in the log_reuse_wait_desc column.

This wait type is usually short lived. SQL Server will automatically create a checkpoint in regular intervals. If this wait type persists, you can execute a checkpoint manually by using the CHECKPOINT statement. You can also adjust the interval between checkpoints, even if only indirectly, by changing the recovery interval server setting.

Summary

Transaction log records of transactions that have been committed or rolled back after the most recent checkpoint prevent log truncation. If this is causing SQL Server to run out of transaction log space, the log_reuse_wait_desc column in sys.databases will return the value CHECKPOINT. This is usually short lived and not necessarily indicative of a problem.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.