8. The transaction log, write-ahead logging, checkpoint and recovery

Write-ahead logging (WAL): every change is first recorded in the transaction log (sequential writes, each record with an LSN — log sequence number). A transaction is committed when its log records are hardened (flushed) to the log file. Data pages are changed in memory and written to data files later.

Checkpoint periodically writes dirty data pages to disk (indirect checkpoints with a target recovery time, default 60 s for new databases), limiting how much log must be replayed after a crash.

Crash recovery on startup (per database):

1. Analysis — read the log from the last checkpoint, find dirty pages and active transactions.
2. Redo — replay committed (and uncommitted) changes not yet in data files (roll forward).
3. Undo — roll back transactions that were active at the crash.

Accelerated Database Recovery (ADR, 2019) keeps row versions in a persistent version store so undo is near-instant and long-running transaction rollbacks don't take hours.

The log is divided into virtual log files (VLFs); space is reused (truncated) only when no longer needed by active transactions, replication, availability groups or — in the FULL model — until a log backup.

Common problem: FULL recovery without log backups → log file grows until the disk is full. Check SELECT name, log_reuse_wait_desc FROM sys.databases.