SQL Server Transaction Log Full: Every Cause, the Right Fix, and What Not to Do

The transaction log full error is one of the most panic-inducing messages in SQL Server administration. Error 9002: “The transaction log for database is full due to LOG_BACKUP.” Everything stops. Users cannot write to the database. ETL jobs fail. The instinct is to fix it fast and the two most common instincts are wrong: switching to SIMPLE recovery mode and shrinking the log file. Both can make a bad situation significantly worse.

The right approach starts with one query that tells you exactly why the log cannot be truncated. That column, log_reuse_wait_desc in sys.databases, is the most important diagnostic tool for this problem. Each value it returns points to a specific cause with a specific fix. Understanding what each value means and what to do about it is the complete solution to this problem.

1 Why the Transaction Log Fills Up Beginner

SQL Server’s transaction log records every change made to the database. It serves two purposes: guaranteeing that committed transactions survive crashes (durability) and enabling point-in-time recovery (restoring the database to any moment in time within the backup chain).

The log file does not grow indefinitely under normal operation because SQL Server reuses space in the log after transactions have been made durable. This reuse is called log truncation and it happens automatically. The key word is automatically, meaning it happens when certain conditions are met. When those conditions are not met, the log cannot be truncated, old space cannot be reused, and the log file grows until it hits its maximum size or fills the volume.

The log_reuse_wait_desc column in sys.databases tells you exactly which condition is preventing truncation. It is not an error column. It is a diagnostic column that names the specific blocker. Every fix for a full transaction log starts by reading this value.

2 The One Query to Run First Beginner

Before touching anything, run this query. It takes one second and tells you everything you need to know about the current log state across all databases.

-- Run this first. Always.
-- log_reuse_wait_desc tells you exactly why the log cannot truncate.

SELECT
    d.name                              AS DatabaseName,
    d.recovery_model_desc               AS RecoveryModel,
    d.log_reuse_wait_desc               AS WhyLogCannotTruncate,
    mf.size * 8.0 / 1024               AS LogSizeMB,
    mf.size * 8.0 / 1024 -
    d.log_size_mb                      AS LogFreeApproxMB,
    CAST(
        (1.0 - (d.log_size_mb /
                NULLIF(mf.size * 8.0 / 1024, 0)))
        * 100 AS DECIMAL(5,1))          AS LogFreePercent
FROM sys.databases d
JOIN sys.master_files mf
    ON  mf.database_id = d.database_id
    AND mf.type_desc   = 'LOG'
ORDER BY LogFreePercent ASC;

-- More precise: use DBCC SQLPERF for current log space
-- (run in the context of the affected database)
DBCC SQLPERF(LOGSPACE);

-- The single most important column: log_reuse_wait_desc
-- Possible values and what they mean:
-- NOTHING               = log can be truncated (truncation pending at next checkpoint)
-- LOG_BACKUP            = waiting for a log backup to occur
-- ACTIVE_TRANSACTION    = an open transaction is holding the log
-- AVAILABILITY_REPLICA  = waiting for AG secondary to harden log records
-- REPLICATION           = waiting for replication log reader
-- DATABASE_MIRRORING    = waiting for mirror to harden log records
-- ACTIVE_BACKUP_OR_RESTORE = backup or restore in progress
-- DATABASE_SNAPSHOT_CREATION = snapshot creation in progress
-- LOG_SCAN              = log scan in progress
-- OTHER_TRANSIENT       = temporary condition, check again shortly

3 LOG_BACKUP: Missing or Infrequent Log Backups Beginner

This is the most common value. A database in FULL or BULK_LOGGED recovery model requires regular log backups for the log to be truncated. Without them the log grows continuously and eventually fills. The log_reuse_wait_desc value of LOG_BACKUP means the log is waiting for a log backup before it can reuse space.

The immediate fix is simple: take a log backup. One log backup immediately frees all the inactive log space that has been accumulating.

-- IMMEDIATE FIX: Take a log backup
-- This frees all inactive log space and allows truncation

BACKUP LOG YourDatabase
TO DISK = 'D:\Backups\YourDatabase_Log_Emergency.trn'
WITH COMPRESSION, STATS = 10;

-- Verify the backup completed and check log space after
DBCC SQLPERF(LOGSPACE);

-- Check log_reuse_wait_desc after the backup
-- It should change to NOTHING if the backup resolved it
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'YourDatabase';

-- ============================================================
-- If LOG_BACKUP keeps returning: you have no log backup job
-- Check when log backups last ran for all databases
-- ============================================================

SELECT
    bs.database_name,
    bs.type,
    MAX(bs.backup_finish_date)          AS LastLogBackup,
    DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE())
                                        AS HoursSinceLastBackup
FROM msdb.dbo.backupset bs
WHERE bs.type = 'L'  -- L = log backup
GROUP BY bs.database_name, bs.type
ORDER BY LastLogBackup ASC;

-- Databases NOT appearing in this result have never had a log backup
-- Databases with LastLogBackup > a few hours need their backup schedule reviewed

4 ACTIVE_TRANSACTION: Long-Running Open Transactions Beginner

The log cannot truncate past the oldest open transaction. If a transaction started three hours ago and is still open, all log records from that transaction forward cannot be reused regardless of how many log backups have run. The log grows to accommodate all the new activity since that transaction started.

-- Find the oldest open transaction and what is holding it open

SELECT
    tran.transaction_id,
    tran.name                           AS TransactionName,
    tran.transaction_begin_time,
    DATEDIFF(MINUTE, tran.transaction_begin_time, GETDATE())
                                        AS OpenMinutes,
    sess.session_id,
    sess.login_name,
    sess.host_name,
    sess.program_name,
    sess.status,
    req.command,
    LEFT(qt.text, 500)                  AS CurrentOrLastQuery,
    sess.open_transaction_count
FROM sys.dm_tran_active_transactions    tran
JOIN sys.dm_tran_session_transactions   stran
    ON stran.transaction_id = tran.transaction_id
JOIN sys.dm_exec_sessions               sess
    ON sess.session_id = stran.session_id
LEFT JOIN sys.dm_exec_requests          req
    ON req.session_id = sess.session_id
OUTER APPLY sys.dm_exec_sql_text(
    ISNULL(req.sql_handle,
           (SELECT TOP 1 sql_handle
            FROM sys.dm_exec_connections
            WHERE session_id = sess.session_id)))   qt
ORDER BY tran.transaction_begin_time ASC;

-- Check how much log space the oldest transaction is consuming
SELECT
    DB_NAME(dtdt.database_id)           AS DatabaseName,
    dtdt.database_transaction_begin_lsn,
    dtdt.database_transaction_log_bytes_used / 1048576.0
                                        AS LogUsedMB,
    dtst.session_id,
    dtst.is_user_transaction
FROM sys.dm_tran_database_transactions  dtdt
JOIN sys.dm_tran_session_transactions   dtst
    ON dtst.transaction_id = dtdt.transaction_id
WHERE dtdt.database_id = DB_ID('YourDatabase')
ORDER BY dtdt.database_transaction_log_bytes_used DESC;

Once you identify the session holding the oldest transaction, you have two choices: wait for it to complete naturally (preferred if it is a legitimate long-running operation near completion) or kill it if it is stuck, abandoned, or causing unacceptable log growth. After the transaction completes or is killed, the next log backup or checkpoint will truncate the freed log space.

5 AVAILABILITY_REPLICA: AG Secondary Falling Behind Intermediate

In an Always On Availability Group, the primary cannot truncate log records until the secondary has hardened them to its own disk. If the secondary falls behind due to network issues, slow storage, or high redo workload, the primary’s log grows to retain all the records the secondary has not yet processed. This is the durability guarantee of synchronous AG working as designed, but it causes the primary’s log to fill when the secondary lag is severe.

-- Check AG secondary health and redo queue lag
SELECT
    ag.name                             AS AGName,
    ar.replica_server_name,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    adbrs.database_name,
    adbrs.synchronization_state_desc,
    adbrs.redo_queue_size               AS RedoQueueKB,
    adbrs.redo_rate                     AS RedoRateKBps,
    adbrs.log_send_queue_size           AS LogSendQueueKB,
    adbrs.log_send_rate                 AS LogSendRateKBps,
    -- Estimated catch-up time in minutes
    CASE WHEN adbrs.redo_rate > 0
         THEN adbrs.redo_queue_size / adbrs.redo_rate / 60.0
         ELSE NULL
    END                                 AS EstCatchUpMinutes
FROM sys.availability_groups            ag
JOIN sys.availability_replicas          ar
    ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars
    ON ars.replica_id = ar.replica_id
JOIN sys.dm_hadr_database_replica_states adbrs
    ON adbrs.replica_id = ar.replica_id
ORDER BY adbrs.redo_queue_size DESC;

-- A large redo_queue_size on the secondary means the primary
-- is retaining log records waiting for the secondary to catch up
-- The log on the primary cannot truncate past the secondary's
-- last hardened LSN

6 REPLICATION: Log Reader Not Keeping Up Intermediate

When transactional replication is enabled, the Log Reader Agent reads committed transactions from the publisher’s transaction log and moves them to the distribution database. The log cannot truncate past the oldest transaction that the Log Reader has not yet processed. If the Log Reader Agent is stopped, slow, or experiencing errors, the publisher’s log grows until the agent catches up.

-- Check replication log reader status and lag

-- Find the oldest replicated transaction not yet sent
SELECT
    DB_NAME(database_id)                AS PublisherDatabase,
    log_reuse_wait_desc,
    -- The oldest unprocessed replication LSN
    active_transaction_start_lsn
FROM sys.dm_db_log_stats(DB_ID('YourDatabase'));

-- Check replication agent job status
SELECT
    j.name                              AS AgentJobName,
    j.enabled,
    CASE last_h.run_status
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
    END                                 AS LastRunStatus,
    CAST(
        CAST(last_h.run_date AS CHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' +
            CAST(last_h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME)                    AS LastRunTime
FROM msdb.dbo.sysjobs j
LEFT JOIN (
    SELECT job_id, run_status, run_date, run_time,
           ROW_NUMBER() OVER (PARTITION BY job_id
                              ORDER BY run_date DESC, run_time DESC) AS rn
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
) last_h ON last_h.job_id = j.job_id AND last_h.rn = 1
WHERE j.name LIKE '%Log Reader%'
   OR j.name LIKE '%repl%'
ORDER BY j.name;

-- If log reader is stopped, start it via SQL Agent
-- or use Replication Monitor in SSMS

7 Other log_reuse_wait_desc Values Beginner

8 Why Switching to SIMPLE Recovery Is Usually Wrong Beginner

The instinct when a log is full is to switch to SIMPLE recovery because SIMPLE recovery truncates the log automatically at every checkpoint. The log shrinks, the crisis is over. The problem is what you lose in exchange.

Switching from FULL to SIMPLE recovery breaks the backup chain. Your previous log backups become useless for point-in-time recovery. If you switch to SIMPLE, take a full backup, then switch back to FULL, you must restart your log backup chain from that new full backup. Any point in time between your last successful log backup and the moment you switched is no longer recoverable.

-- Check recovery model and when the last full backup ran
-- BEFORE making any recovery model changes

SELECT
    d.name,
    d.recovery_model_desc,
    MAX(bs.backup_finish_date)          AS LastFullBackup,
    MAX(ls.backup_finish_date)          AS LastLogBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON  bs.database_name = d.name
    AND bs.type = 'D'  -- full backup
LEFT JOIN msdb.dbo.backupset ls
    ON  ls.database_name = d.name
    AND ls.type = 'L'  -- log backup
WHERE d.name = 'YourDatabase'
GROUP BY d.name, d.recovery_model_desc;

-- If you do need to change recovery model (rare, justified cases only)
-- Document the decision and immediately take a full backup after

-- Step 1: Switch to SIMPLE (only if explicitly justified)
ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;

-- Step 2: If switching back to FULL, take a full backup FIRST
-- to restart the backup chain before scheduling log backups
BACKUP DATABASE YourDatabase
TO DISK = 'D:\Backups\YourDatabase_Full_AfterRecoveryChange.bak'
WITH COMPRESSION, STATS = 10;

-- Step 3: Switch back to FULL
ALTER DATABASE YourDatabase SET RECOVERY FULL;

-- Step 4: Schedule log backups immediately
-- Point-in-time recovery only works from this new full backup forward

9 Why Shrinking the Log Makes It Worse Beginner

DBCC SHRINKFILE on the log file releases physical space back to the OS. The log file becomes smaller. Within hours or days, as the database resumes normal activity, the log grows back to roughly its previous size through autogrowth events. Each autogrowth event causes a performance stall. If autogrowth is configured as a small fixed size or a percentage, dozens of small growth events occur during the growth-back period.

Shrinking the log is almost never the right action. Pre-sizing the log to an appropriate size and configuring large fixed-increment autogrowth as a safety net is the right approach. Shrink is appropriate in one specific scenario: a log grew unusually large due to a one-time event (large bulk load, accidental index rebuild on a massive table) and you are certain the large size will not recur. In that case, shrink once, then immediately pre-size to a reasonable working size.

-- The right way to handle a log that grew too large after a one-time event

-- Step 1: Take a log backup first to clear inactive log space
BACKUP LOG YourDatabase
TO DISK = 'D:\Backups\YourDatabase_Log_PreShrink.trn'
WITH COMPRESSION;

-- Step 2: Check current VLF count and log space
DBCC LOGINFO('YourDatabase');  -- count the rows
DBCC SQLPERF(LOGSPACE);

-- Step 3: Shrink to a reasonable size (only if the growth was one-time)
USE YourDatabase;
DBCC SHRINKFILE(YourDatabase_Log, 2048);  -- shrink to 2 GB

-- Step 4: IMMEDIATELY pre-size to your target working size
-- This prevents dozens of small autogrowth events during growth-back
ALTER DATABASE YourDatabase
MODIFY FILE (
    NAME       = YourDatabase_Log,
    SIZE       = 8192MB,      -- pre-size to expected working size
    FILEGROWTH = 1024MB       -- large fixed increment, not percentage
);

-- Step 5: Verify VLF count is reasonable after resize
-- Over 1000 VLFs indicates the file grew in many small increments
DBCC LOGINFO('YourDatabase');

10 How to Size and Configure the Log File Correctly Intermediate

Most full transaction log incidents are preventable with correct initial sizing and configuration. The log file should be pre-sized close to its expected working size. Autogrowth should use a large fixed increment and serve only as an emergency safety net, not as the normal growth mechanism.

-- Recommended log file configuration
ALTER DATABASE YourDatabase
MODIFY FILE (
    NAME       = YourDatabase_Log,
    -- Pre-size to expected working size
    -- For OLTP: typically 25-50% of data file size
    -- For data warehouses: may need to be larger during ETL windows
    SIZE       = 10240MB,     -- 10 GB starting point for medium OLTP
    -- Large fixed increment: prevents many small growth events
    -- Never use percentage growth (10% of 100 GB = 10 GB growth event)
    FILEGROWTH = 2048MB,      -- 2 GB fixed increment
    MAXSIZE    = UNLIMITED
);

-- Check VLF count after configuration
-- Each autogrowth event creates additional VLFs
-- Too many VLFs (over 1000) can affect startup, backup, and log restore times
DBCC LOGINFO('YourDatabase');

-- Calculate appropriate log backup frequency
-- Log backup frequency should be based on your RPO (recovery point objective)
-- RPO = maximum acceptable data loss in minutes
-- If you can lose at most 15 minutes of data, take log backups every 15 minutes
-- If you can lose at most 1 hour of data, take log backups every 30-60 minutes
-- More frequent log backups = smaller log file needed (log truncates more often)

11 Monitoring Log Space Before It Becomes an Incident Beginner

A full transaction log is always preceded by a growing transaction log. Monitoring log space usage and alerting at a threshold (80% or 90% used) gives you time to investigate and act before the log fills completely and writes stop.

-- ============================================================
-- Log space monitoring query
-- Run as a scheduled SQL Agent job, alert when threshold exceeded
-- ============================================================

SELECT
    d.name                              AS DatabaseName,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    mf.size * 8.0 / 1024               AS LogSizeMB,
    -- Calculate used space
    (mf.size * 8.0 / 1024) -
    (SELECT SUM(log_space_used)
     FROM (
         SELECT
             d2.database_id,
             mf2.size * 8.0 / 1024 -
             (SELECT cntr_value FROM sys.dm_os_performance_counters
              WHERE counter_name = 'Log File(s) Size (KB)'
              AND instance_name = d2.name) / 1024.0 AS log_space_used
         FROM sys.databases d2
         JOIN sys.master_files mf2
             ON mf2.database_id = d2.database_id
             AND mf2.type_desc = 'LOG'
         WHERE d2.database_id = d.database_id
     ) x)                              AS LogFreeMB,
    CAST(
        (SELECT cntr_value
         FROM sys.dm_os_performance_counters
         WHERE counter_name = 'Log File(s) Used Size (KB)'
         AND instance_name = d.name)
        * 100.0 /
        NULLIF(
        (SELECT cntr_value
         FROM sys.dm_os_performance_counters
         WHERE counter_name = 'Log File(s) Size (KB)'
         AND instance_name = d.name), 0)
        AS DECIMAL(5,1))               AS LogUsedPct
FROM sys.databases d
JOIN sys.master_files mf
    ON  mf.database_id = d.database_id
    AND mf.type_desc   = 'LOG'
WHERE d.database_id > 4  -- user databases only
ORDER BY LogUsedPct DESC;

-- Simplified version using DBCC SQLPERF
-- Store results in a monitoring table and alert on threshold
CREATE TABLE dbo.LogSpaceHistory (
    LogDate         DATETIME2 DEFAULT SYSDATETIME(),
    DatabaseName    NVARCHAR(128),
    LogSizeMB       DECIMAL(10,2),
    LogSpaceUsedPct DECIMAL(5,2),
    Status          NVARCHAR(50)
);

INSERT INTO dbo.LogSpaceHistory (DatabaseName, LogSizeMB, LogSpaceUsedPct, Status)
SELECT
    [Database Name],
    [Log Size (MB)],
    [Log Space Used (%)],
    CASE
        WHEN [Log Space Used (%)] >= 90 THEN 'CRITICAL'
        WHEN [Log Space Used (%)] >= 80 THEN 'WARNING'
        ELSE 'OK'
    END
FROM sys.dm_exec_query_results_cache
-- Use DBCC SQLPERF output
-- Schedule this as a SQL Agent job every 15 minutes
-- Alert when Status = CRITICAL

References

• Microsoft Docs: Troubleshoot a Full Transaction Log (SQL Server Error 9002)
• Microsoft Docs: The Transaction Log (SQL Server)
• Microsoft Docs: Manage the Size of the Transaction Log File
• Microsoft Docs: sys.databases (log_reuse_wait_desc)
• Microsoft Docs: sys.dm_db_log_stats
• SQLYARD: Always On Availability Groups Complete Guide
• SQLYARD: SQL Server Fragmentation Guide (File Sizing and Autogrowth)
• SQLYARD: SQL Server Agent Jobs: How to Know About Failures Before the Business Does
• SQLYARD: SQL Server Instance Setup and Best Practices
• SQLYARD: How SQL Server Transaction Log Backup, Truncate and Shrink Functions Operate (2022)