SQL Server Transaction Log Full

The important part of that message is the bit in quotes. LOG_BACKUP is not the problem, it is the diagnosis, and SQL Server has already told you which of about a dozen causes you have. Most advice online skips straight to shrinking the log, which fixes nothing and often makes the next outage worse.

Read the Reason, Not the Size

SELECT  name,
        log_reuse_wait_desc,
        recovery_model_desc,
        state_desc
FROM    sys.databases
WHERE   name = N'YourDb';

On the lab: every database in FULL recovery that has not had a log backup shows LOG_BACKUP, model included. The SIMPLE ones show NOTHING.

log_reuse_wait_desc is the answer. What it means:

Re-run that query after every action you take. The column reports what reuse was waiting on as of the last checkpoint, so clearing one blocker often just promotes the next. Reproduced on a test database with a capped log: a log backup moved the value from LOG_BACKUP to ACTIVE_TRANSACTION and the log stayed at 100% used, because the backup could not truncate past an open transaction.

Microsoft's reference covers MSSQLSERVER_9002 and sys.databases in full: the table above is the 7 values you will actually meet, the catalog view documents all 13.

The Two Common Ones

LOG_BACKUP

The database is in FULL recovery and nobody is backing up the log. In FULL recovery, the log is only truncated by a log backup. A full backup does not do it, however many you take. This is the single most common cause of a full log.

BACKUP LOG [YourDb] TO DISK = N'D:\Backups\YourDb_log.trn';

Space becomes reusable immediately, the file does not shrink, but the space inside it is now free, which is what you want.

If the database does not need point-in-time recovery, the honest fix is to stop pretending it does:

ALTER DATABASE [YourDb] SET RECOVERY SIMPLE;

Do not do that on anything you would need to restore to a point in time. It breaks the log chain and your recovery point becomes the last full or differential backup. If in doubt, take log backups instead.

ACTIVE_TRANSACTION

Something has a transaction open and the log cannot be truncated past it. Find it in the database the 9002 named:

SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.status                AS session_status,
        t.transaction_begin_time,
        DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS open_minutes,
        dt.database_transaction_log_bytes_used,
        r.status                AS request_status,
        r.command,
        CASE WHEN r.sql_handle IS NULL THEN qt.text
             ELSE SUBSTRING(qt.text, (r.statement_start_offset / 2) + 1,
                      ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
                             ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1)
        END AS sql_text
FROM    sys.dm_tran_active_transactions t
JOIN    sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN    sys.dm_tran_database_transactions dt ON dt.transaction_id = t.transaction_id
JOIN    sys.dm_exec_sessions s ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
                                   AND c.net_transport <> 'Session'
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) qt
WHERE   dt.database_id = DB_ID(N'YourDb')
ORDER BY t.transaction_begin_time;

Look for an old transaction_begin_time, then read 2 columns. session_status reads sleeping on the session you want, an application that opened a transaction and never committed, usually an error path that missed its rollback. request_status is NULL on that same row, because a sleeping session is not running anything and so has no row in sys.dm_exec_requests at all. sql_text still shows something for it: with no request to read, the query falls back to the connection’s most_recent_sql_handle, the last batch that session sent, which is the closest you get to what it was doing when it stopped. database_transaction_log_bytes_used is how much of the log that one transaction is pinning. A sleeping session with an open transaction is the classic cause.

Also check for the oldest active transaction specifically:

DBCC OPENTRAN('YourDb');

Killing the session rolls the transaction back, and the rollback also needs log space and time, sometimes more than the original work. Understand what it was doing before you kill it.

Getting Out of It Right Now

If the log file is genuinely out of disk and you cannot back it up:

First, free disk space, or add a second log file on another drive as a temporary measure:

ALTER DATABASE [YourDb] ADD LOG FILE
    (NAME = N'YourDb_log2',
     FILENAME = N'E:\Logs\YourDb_log2.ldf',
     SIZE = 4GB);

Then take the log backup.

Remove the temporary file afterwards, which takes more than one statement: DBCC SHRINKFILE (YourDb_log2, EMPTYFILE), then another log backup, then ALTER DATABASE [YourDb] REMOVE FILE YourDb_log2. While any part of the active log still sits in that file the remove raises Msg 5042, the file cannot be removed because it is not empty, so repeat the emptyfile and the backup until it goes. A permanent second log file is not a fix, it is a reminder of an incident.

Never use WITH TRUNCATE_ONLY or NO_LOG. They were removed for good reason: they break the log chain silently, so your next restore fails at exactly the moment you need it.

Why Shrinking Is Not the Fix

Shrinking the log after a backup will reclaim disk, but if the log grew to 40GB it grew for a reason, and it will grow again, this time in a hurry, during production, with autogrowth pauses.

Size the log for the workload’s peak and leave it there. A log at a stable large size is healthy. A log that shrinks and regrows repeatedly causes VLF fragmentation and stalls.

Stopping It Recurring

• Log backups every 15 minutes on anything in FULL recovery. If a database does not deserve log backups, it does not deserve FULL recovery.
• Alert on log percent used, not on disk full. By the time the disk is full you are already down.
• Alert on transactions open longer than a few minutes. That catches the ACTIVE_TRANSACTION case before it becomes an outage.
• Check log_reuse_wait_desc across all databases on a schedule, so you see the drift early.

Frequently Asked Questions

Related Scripts

• msdb Is Growing: Purge Backup, Job and Mail History Safely, when the database named in the 9002 is msdb and the open transaction is your own purge
• Get Log Reuse Waits, the reason for every database at once
• Disk Is Full or the Log Will Not Truncate, the router that tells you whether it is the log, a data file or the drive
• Get Transaction Log Size and Usage
• Get VLF Counts, the fragmentation that shrink-and-regrow causes
• Recovery Models Explained, why the database was in FULL recovery to begin with