SQL Interview: 111 Simple recovery and log truncation

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:

If a database is configured for simple recovery model, what could stop the truncation of the transaction log being effective, and for the transaction log to keep growing in size?

Answer:

Here are the most frequent reasons:

Cause	Description
Long-running transactions	SQL Server can’t truncate the log until the oldest active transaction finishes.
Open transactions in other sessions	Even idle connections with an open transaction keep old log records active.
Long-running operations (e.g., index rebuilds, bulk loads)	These can generate large amounts of log activity.
Replication, Change Data Capture, or Availability features	Features like replication, CDC, or even database snapshots delay log truncation until they’ve read the relevant log records.
Large or frequent autogrowth events	If the workload suddenly spikes, the log can grow before truncation occurs.
High volume of DML in a short period	Checkpoints may not occur fast enough to reuse log space during bursts of writes.

2026-01-16