SQL Server "The transaction log for database is full" (error 9002): read log_reuse_wait_desc

1. How bad is it?

SELECT DB_NAME(database_id) AS db,
       total_log_size_in_bytes / 1048576 AS total_mb,
       used_log_space_in_bytes / 1048576 AS used_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

SELECT name, physical_name, size / 128 AS size_mb, max_size, growth, is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG';

That last query needs USE shop; first. Run DBCC SQLPERF(LOGSPACE); for an overview of every database. A max_size other than -1 means someone capped it, and the log may be full with plenty of disk space.

2. LOG_BACKUP: back up the log

BACKUP LOG shop TO DISK = 'D:\Backups\shop_log.trn';

Check again, and the used percentage should fall. The file itself stays the same size, as that’s not what a backup does, and that’s fine: the free space inside gets reused.

Then schedule log backups, or this returns. It’s what the full recovery model is for. Every 15 minutes is common.

If the disk is full and there’s nowhere to write the backup, use another volume or a network share. The account running the SQL Server service needs rights there.

You don’t need point-in-time restore? Then the simple recovery model is the honest choice, as SQL Server truncates the log on its own:

ALTER DATABASE shop SET RECOVERY SIMPLE;

The trade-off is that you can only restore to your last full or differential backup. So on production, that’s a decision, not a quick fix.

3. ACTIVE_TRANSACTION: something never finished

An open transaction holds the log from where it started, however little it did. Find it:

DBCC OPENTRAN('shop');

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       t.database_transaction_begin_time,
       t.database_transaction_log_bytes_used / 1048576 AS log_mb
FROM sys.dm_tran_database_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
WHERE t.database_id = DB_ID('shop')
ORDER BY t.database_transaction_begin_time;

The usual suspects are someone’s forgotten BEGIN TRAN in SSMS, or an application’s bug. A large DELETE or index rebuild that’s still running belongs here too, and that one you should probably let finish.

Ask its owner, or end it, after checking who it belongs to:

KILL 72;

A rollback uses the log as well, and may take as long as the work did. Don’t restart SQL Server to speed it up, as recovery then has to do the same work at startup, with the database unavailable.

For a huge delete, do batches, so that the log can be reused between them:

WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM events WHERE created_at < '2024-01-01';
    IF @@ROWCOUNT = 0 BREAK;
END;

In the full model, keep your log backups running during this.

4. Replication and Availability Groups

The log can’t be reused until the other side has it, so a replica that’s stopped or far behind holds everything.

SELECT ar.replica_server_name, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id;

Restore that connection, or the Log Reader Agent for replication. As a last resort, if a replica can’t be saved, remove it from the group, and add it again later. CDC can hold the log the same way, when its capture job isn’t running.

5. NOTHING: out of room

The log can reuse its space, and still it can’t grow any more. Add room in one of these ways:

-- raise the cap
ALTER DATABASE shop MODIFY FILE (NAME = shop_log, MAXSIZE = UNLIMITED);

-- or add a log file on a volume that has space
ALTER DATABASE shop ADD LOG FILE
  (NAME = shop_log2, FILENAME = 'E:\Logs\shop_log2.ldf', SIZE = 1GB, FILEGROWTH = 512MB);

Remove that extra file when you’ve recovered, with ALTER DATABASE shop REMOVE FILE shop_log2;.

Then look at the disk itself. On Linux, see no space left on device.

6. Should I shrink it?

Only after the cause is fixed, and only once, for a log that grew because of a mistake:

USE shop;
DBCC SHRINKFILE (shop_log, 2048);

Set a sensible size and growth, as in FILEGROWTH = 512MB. Don’t put shrinking in a scheduled job: the log just has to grow back during busy times, and writes pause while it does.