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';

log_reuse_wait_desc is the answer. What it means:

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:

SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        t.transaction_begin_time,
        DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS open_minutes,
        r.status,
        r.command,
        SUBSTRING(qt.text, (r.statement_start_offset/2)+1, 4000) AS running_statement
FROM    sys.dm_tran_active_transactions t
JOIN    sys.dm_tran_session_transactions st ON st.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
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) qt
ORDER BY t.transaction_begin_time;

Look for an old transaction_begin_time and a session whose status is sleeping, that is an application that opened a transaction and never committed, usually because of an error path that missed its rollback. 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:

• 1. 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);

• 2. Take the log backup.
• 3. Remove the temporary file afterwards, 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.

Common Questions

Related Scripts

• Get Log Reuse Waits, the reason for every database at once
• 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