Why is my transaction log full?

In one line: the log can only be reused once nothing needs its records any more. When something still does (a missing log backup, an open transaction, a replica behind), the log grows until the disk is full, and log_reuse_wait_desc tells you which one it is.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/transaction-log-full on GitHub.

What you’ll see

  • Msg 9002, “The transaction log for database ‘…’ is full”.
  • A log file that keeps growing, or a disk alert on the log drive.
  • A DBCC SHRINKFILE that gives nothing back.

Why it happens

The two causes the demo reproduces:

  • LOG_BACKUP: FULL recovery and no log backups. In FULL recovery, log records are kept until a log backup has copied them. A database that only gets full backups keeps every record. In the demo, ~228 MB stayed in use after a CHECKPOINT. A log backup brought it to 5 MB.
  • ACTIVE_TRANSACTION: one open transaction. Even in SIMPLE recovery, the log is truncated only up to the start of the oldest open transaction. One idle session that wrote one row pinned 226 MB of everybody else’s committed work.

Other values you may meet: REPLICATION (replication or CDC hasn’t read the log), AVAILABILITY_REPLICA (a secondary is behind), ACTIVE_BACKUP_OR_RESTORE, and CHECKPOINT.

What to do

  • Ask why first: sys.databases.log_reuse_wait_desc. The fix follows from the answer.
  • LOG_BACKUP: schedule log backups, or switch to SIMPLE recovery if you don’t need point-in-time restore. A full backup doesn’t count.
  • ACTIVE_TRANSACTION: find the oldest transaction and its session, and end it.
  • Then shrink, if you must, and size the log for its real peak, growing it in large steps. In the demo, 1 MB autogrowth produced 517 virtual log files. 256 MB growth produced 20 for the same log. Thousands of VLFs slow startup and restore.

What doesn’t help

  • CHECKPOINT in FULL recovery. It freed nothing: 228.5 MB before and after.
  • Shrinking a log that’s still held. It gave nothing back, and the file grew from 256 to 272 MB while other work kept writing.
  • Trusting log_reuse_wait_desc alone. It reports the last truncation attempt, so it can lag behind. Check the used space too.

Check your server

-- Why each log can't be reused, and the oldest open transaction holding it.
SELECT  d.name, d.recovery_model_desc, d.log_reuse_wait_desc,
        OldestTranBegin   = MIN(dt.database_transaction_begin_time),
        OldestTranSession = (SELECT TOP (1) COALESCE(st.session_id, r.session_id)
                             FROM sys.dm_tran_database_transactions AS x
                             LEFT JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = x.transaction_id
                             LEFT JOIN sys.dm_exec_requests AS r ON r.transaction_id = x.transaction_id
                             WHERE x.database_id = d.database_id AND x.database_transaction_begin_time IS NOT NULL
                             ORDER BY x.database_transaction_begin_time)
FROM    sys.databases AS d
LEFT JOIN sys.dm_tran_database_transactions AS dt
       ON dt.database_id = d.database_id AND dt.database_transaction_begin_time IS NOT NULL
GROUP BY d.database_id, d.name, d.recovery_model_desc, d.log_reuse_wait_desc;

See it happen

The transaction-log-full demo fills a log on purpose with each cause and measures it at every step. It also shows the shrink that doesn’t work and counts VLFs under small and large autogrowth.

Related: backup and restore · minimal logging · Accelerated Database Recovery · blocking chains

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights