Log truncation is blocked

What this check looks for

Online, non-snapshot databases whose log_reuse_wait_desc is one of the values that means something is holding the log, and whose log is already more than 50 percent full.

The 50 percent condition is deliberate. log_reuse_wait_desc is almost never NOTHING on a busy instance, because any open transaction puts it into ACTIVE_TRANSACTION for as long as that transaction runs. A blocked truncation on a nearly empty log is a normal moment rather than a problem. A blocked truncation on a log that is already half full is a countdown.

The message names the database, the reason SQL Server gave, and how full the log is.

Why it matters

Left alone, the log grows until the drive fills, and by the time it does the original cause is usually gone.

That is the difficulty with this one. The outage is error 9002 at three in the morning, and the investigation that follows finds a full drive and a large log file and no explanation, because the transaction that caused it committed hours earlier. The evidence is only available while it is happening, which is what this check is for.

Shrinking the log afterwards treats the symptom and guarantees a repeat, usually with worse VLF fragmentation than before.

The reason column is the whole diagnosis

How to confirm it yourself

SELECT d.[name],
       d.[recovery_model_desc],
       d.[log_reuse_wait_desc],
       CAST(lsu.[used_log_space_in_percent] AS DECIMAL(5,1)) AS [log_used_pct],
       CAST(lsu.[total_log_size_in_bytes] / 1024.0 / 1024 AS DECIMAL(10,1)) AS [log_size_mb]
  FROM sys.databases AS d WITH (NOLOCK)
 CROSS APPLY sys.dm_db_log_space_usage AS lsu
 WHERE d.[database_id] = DB_ID()
 ORDER BY d.[name];

sys.dm_db_log_space_usage is per database, so to sweep the instance either run it in each database or use the older instance-wide view:

SELECT [name], [log_reuse_wait_desc], [recovery_model_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [log_reuse_wait_desc] <> 'NOTHING'
 ORDER BY [name];

For ACTIVE_TRANSACTION, find the session:

SELECT tst.[session_id],
       tat.[name],
       tat.[transaction_begin_time],
       DATEDIFF(MINUTE, tat.[transaction_begin_time], GETDATE()) AS [minutes_open],
       s.[login_name], s.[host_name], s.[program_name], s.[status]
  FROM sys.dm_tran_active_transactions AS tat WITH (NOLOCK)
 INNER JOIN sys.dm_tran_session_transactions AS tst WITH (NOLOCK)
         ON tst.[transaction_id] = tat.[transaction_id]
 INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
         ON s.[session_id] = tst.[session_id]
 WHERE tst.[is_user_transaction] = 1
 ORDER BY tat.[transaction_begin_time];

How to fix it

Fix the reason, then let SQL Server reuse the space it already has. The log does not need shrinking in most cases; it needs the block removed so the existing space becomes reusable.

• LOG_BACKUP: take a log backup, and then find out why the scheduled one is not running. If the database does not need point in time recovery, switching it to simple recovery is a legitimate answer, made deliberately rather than by accident.
• ACTIVE_TRANSACTION: find the session and deal with it. See the long open transaction check, which covers this in its own right.
• REPLICATION: restart the stalled agent. If replication was removed without being cleaned up properly, sp_removedbreplication is what releases the log.
• AVAILABILITY_REPLICA: resume or remove the suspended replica. The log will not move until the secondary does.
• OLDEST_PAGE: check target_recovery_time on the database.

Only shrink the log once the cause is fixed, and only if it grew to a size you genuinely do not need. Then grow it back in sensible chunks, because a shrink and regrow cycle is how VLF counts get out of hand.

How long it takes

About an hour and a half, most of it diagnosis. The fix itself is usually one statement.

Related reports

Related checks

Frequently asked questions

Why not report every database with a blocked truncation? Because on a busy instance that is most of them, most of the time, and a report that fires constantly is a report nobody reads. The log being over half full is what turns it from a moment into a trend.

I took a log backup and the reason did not change. Then it was not LOG_BACKUP. Read the column again: it reports one reason at a time, and clearing one can reveal the next.

Can I just shrink the log? It will work once and then happen again, and each cycle adds VLFs. Fix the reason first.

The database is in simple recovery and still says LOG_BACKUP. That combination means the recovery model was changed very recently and the value has not been refreshed, or there is an active backup running. Re-check it in a minute.