Understanding SQL Server log_reuse_wait

Imagine getting a disk alert at 2 AM because a transaction log has filled the drive. Before you can fix anything, you need to understand why it grew in the first place. SQL Server gives you that answer in a single column: log_reuse_wait_desc in sys.databases.

Understanding what each value means is one of the most practical troubleshooting skills a SQL Server DBA can develop.

What is log_reuse_wait_desc?

SQL Server writes every change to the transaction log before it writes it to the data files. Once a transaction is committed and safely on disk, that portion of the log becomes eligible for reuse, meaning that space can be overwritten by new log records. This is called log truncation.

Truncation does not happen automatically at will. The engine checks whether certain conditions are met before it can safely mark log space as reusable. When a condition is blocking truncation, log_reuse_wait_desc tells you which one.

A growing log is almost always explained by one of the wait types below.

Quick Diagnostic Query

Run this on the instance to see log size, usage, and the blocking wait type for every user database at once:

SELECT
    d.name                                          AS DatabaseName,
    d.recovery_model_desc                           AS RecoveryModel,
    d.log_reuse_wait_desc                           AS LogReuseWait,
    CAST(ls.cntr_value / 1024.0 AS DECIMAL(10,1))  AS LogSizeMB,
    CAST(lu.cntr_value / 1024.0 AS DECIMAL(10,1))  AS LogUsedMB,
    CAST(
        lu.cntr_value * 100.0
        / NULLIF(ls.cntr_value, 0)
        AS DECIMAL(5,1))                            AS PctUsed
FROM sys.databases d
JOIN sys.dm_os_performance_counters ls
    ON ls.instance_name = d.name
    AND ls.counter_name = 'Log File(s) Size (KB)'
    AND ls.object_name  LIKE '%Databases%'
JOIN sys.dm_os_performance_counters lu
    ON lu.instance_name = d.name
    AND lu.counter_name = 'Log File(s) Used Size (KB)'
    AND lu.object_name  LIKE '%Databases%'
WHERE d.database_id > 4     -- exclude master, model, msdb, tempdb
ORDER BY PctUsed DESC;

Sample output using synthetic database names:

DatabaseName  RecoveryModel  LogReuseWait          LogSizeMB  LogUsedMB  PctUsed
------------  -------------  --------------------  ---------  ---------  -------
SalesDB       FULL           LOG_BACKUP            10240.0    9830.0     96.0
AppDB         FULL           ACTIVE_TRANSACTION    2048.0     1740.0     85.0
ReportDB      SIMPLE         NOTHING               1024.0     102.0      10.0

Wait Type Reference

NOTHING

The log has been successfully truncated and space is available for reuse. This is the healthy, normal state. No action is needed.

LOG_BACKUP

What it means: The database is in FULL or BULK_LOGGED recovery model and no log backup has run recently enough to allow truncation. Until the log is backed up, it cannot be truncated.

Why it matters: In FULL recovery, every transaction is retained in the log until a log backup reads it. If log backups stop running for any reason (a failed job, a missed schedule, or the database not yet added to a backup plan), the log will grow continuously until disk space is exhausted.

What to do:

• Check when the last log backup ran:

SELECT
    database_name,
    MAX(backup_finish_date) AS LastLogBackup
FROM msdb.dbo.backupset
WHERE type = 'L'
  AND database_name = 'SalesDB'
GROUP BY database_name;

• If no recent backup exists, take one now:

BACKUP LOG SalesDB
TO DISK = '\\fileserver\share\path\SalesDB_log.bak'
WITH COMPRESSION, STATS = 10;

for the backup job covering this database.

• Investigate why scheduled log backups stopped. Check the SQL Agent job history

either set up log backups immediately or switch to SIMPLE recovery if point-in-time restore is not required:

• If this is a newly created database in FULL recovery with no backup plan yet,

ALTER DATABASE SalesDB SET RECOVERY SIMPLE;
CHECKPOINT;

ACTIVE_TRANSACTION

What it means: An open, uncommitted transaction is holding the log open. The log cannot truncate past the oldest active transaction's begin point.

Why it matters: A long-running transaction, a forgotten BEGIN TRAN without a matching COMMIT or ROLLBACK, or an application that holds transactions open while waiting for user input can hold the log open indefinitely.

What to do:

• Find the oldest open transaction and the session holding it:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status                                AS SessionStatus,
    r.status                                AS RequestStatus,
    r.command,
    r.wait_type,
    r.wait_time / 1000                      AS WaitSeconds,
    DATEDIFF(MINUTE, dt.database_transaction_begin_time, GETDATE())
                                            AS TranAgeMinutes,
    dt.database_transaction_log_bytes_used  AS LogBytesUsed,
    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        (CASE r.statement_end_offset
             WHEN -1 THEN DATALENGTH(st.text)
             ELSE r.statement_end_offset
         END - r.statement_start_offset) / 2 + 1
    )                                       AS CurrentStatement
FROM sys.dm_tran_database_transactions dt
JOIN sys.dm_tran_session_transactions  st2 ON st2.transaction_id = dt.transaction_id
JOIN sys.dm_exec_sessions              s   ON s.session_id = st2.session_id
LEFT JOIN sys.dm_exec_requests         r   ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE dt.database_id = DB_ID('AppDB')
ORDER BY dt.database_transaction_begin_time ASC;

• If the session is a runaway query or an abandoned transaction, end it:

KILL <session_id>;   -- replace <session_id> with the value from the query above

open transaction. Common causes: missing error handling that skips ROLLBACK, application waiting for user input inside a transaction, or a deadlock that left a transaction open.

• If the session belongs to an application, investigate why it is holding an

ACTIVE_BACKUP_OR_RESTORE

What it means: A backup or restore operation is currently running on this database. The log cannot be truncated while the operation is in progress.

What to do: Wait for the backup or restore to complete. If it has been running for longer than expected, check sys.dm_exec_requests for the backup session and sys.dm_io_virtual_file_stats for I/O throughput to see whether it is stalled.

AVAILABILITY_REPLICA

What it means: The database is participating in a high availability configuration (such as Always On Availability Groups). The log cannot be truncated until the secondary replica has hardened the log records.

Why it matters: If a secondary replica is behind (due to network latency, high redo load on the secondary, or a disconnected replica), the primary log will accumulate records that the secondary has not yet processed.

What to do:

• Check the synchronization state and redo queue size:

SELECT
    ag.name                             AS AGName,
    ar.replica_server_name              AS ReplicaServer,
    drs.database_id,
    DB_NAME(drs.database_id)            AS DatabaseName,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.redo_queue_size                 AS RedoQueueKB,
    drs.log_send_queue_size             AS SendQueueKB,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas           ar  ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups             ag  ON ag.group_id   = ar.group_id
ORDER BY drs.redo_queue_size DESC;

to grow until it reconnects and catches up.

• If a replica shows NOT_SYNCHRONIZING or a large redo queue:
• Check network connectivity between primary and secondary.
• Check resource pressure (CPU, disk I/O) on the secondary.
• If the secondary is intentionally offline, the primary log will continue

becoming a disk risk, consider removing the secondary temporarily from the AG and re-joining it after the primary log is under control. This is a significant operational step, so confirm with your team before proceeding.

• If a secondary has been offline long enough that the primary log growth is

REPLICATION

What it means: Transactional replication is configured on this database and the Log Reader Agent has not yet read all transactions from the log. The log cannot truncate past the last record the Log Reader has processed.

What to do:

• Check for pending replicated transactions:

SELECT
    pending_xact_id,
    entry_time,
    originator_id,
    originator_lsn
FROM sys.dm_replication_pending_xacts
ORDER BY entry_time ASC;

If it is stopped or failing, restart it and investigate the job history for error messages.

• Check whether the Log Reader Agent job is running and healthy in SQL Agent.

will continue to grow until the subscriber catches up. Check replication monitor for distributor-to-subscriber latency.

• If replication latency is high due to a slow subscriber, the primary log

CHECKPOINT

What it means: In SIMPLE or BULK_LOGGED recovery, the log is truncated at the next checkpoint. If a checkpoint has not yet occurred since the last truncatable log records were written, this wait appears.

What to do: In most cases, this resolves itself quickly, as SIMPLE recovery issues checkpoints frequently. If it persists, run a manual checkpoint in the database context:

USE AppDB;
CHECKPOINT;

If it still persists after a checkpoint, check for an open transaction using the ACTIVE_TRANSACTION query above. An open transaction can block checkpoint advancement even in SIMPLE recovery.

DATABASE_MIRRORING

What it means: Database mirroring is configured and the mirror server has not yet hardened the log records. Similar to AVAILABILITY_REPLICA but for the older database mirroring technology.

Note: Database mirroring is deprecated since SQL Server 2012 and removed in SQL Server 2022. If you see this on a SQL Server 2019 or earlier instance, consider migrating to Availability Groups.

What to do: Check mirror status with sys.database_mirroring and investigate the mirror server connectivity and synchronization lag.

Key Takeaways

Run the quick diagnostic query before doing anything else.

• log_reuse_wait_desc is your first diagnostic step for any log space problem.

straightforward: take a log backup and confirm your scheduled log backup jobs are running reliably.

• LOG_BACKUP is the most common cause on FULL recovery databases. The fix is

or long-running transaction can hold a log open indefinitely and grow it past any reasonable size. Identify and address the session, then investigate the root cause in the application.

• ACTIVE_TRANSACTION is the most operationally significant cause. An abandoned

your secondary components before taking action. The primary log will keep growing until the secondary catches up.

• AVAILABILITY_REPLICA and REPLICATION require understanding the health of

physical log file has grown large and not been reclaimed. Consider a controlled DBCC SHRINKFILE after you have confirmed the cause of the prior growth is resolved. Only shrink after the growth is under control — never as a first response.

• NOTHING with high log usage means the log has been truncated but the