SQL Interview: 102 Checkpoint writes

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Advanced

Question:

When checkpoint writes occur, is only committed data written, or is uncommitted data written?

If only committed data, explain why.

If both, explain why.

Answer:

Checkpoint is a background process that:

Flushes dirty pages (pages in memory that have been modified since they were read from disk) to disk.
Records a log sequence number (LSN) in the transaction log that marks the point at which the database is known to be consistent on disk.

This means that SQL Server doesn’t flush only committed transactions — it flushes all dirty pages, whether the transactions that modified them are committed or not.

The reason is performance and recovery consistency. SQL Server uses write-ahead logging (WAL). That means log records are always written to disk before the associated data pages are flushed.

Because of WAL, it’s safe to write uncommitted pages to disk — their changes can be rolled back later using the transaction log during crash recovery.

If a crash happens: