The transaction log for database is full due to 'LOG_BACKUP'

What it means

Every change is written to the database’s transaction log first. SQL Server reuses log space once nothing needs the old records any more; until then, the file has to grow. Error 9002 means it can’t grow (it reached its MAXSIZE, autogrowth is off, or the disk is full) and nothing in it can be reused yet. The database stays online and readable, but every statement that writes fails until you free some log space.

Msg 9002, Level 17, State 2, Server 54e64f9ad96b, Line 5
The transaction log for database 'shop' is full due to 'LOG_BACKUP' and the holdup lsn is (42:264:1).

SQL Server 2022 and 2025 add the holdup lsn, the log sequence number holding the log up; 2019 ends the message after 'LOG_BACKUP'. The word in quotes is the important part. It’s the database’s log_reuse_wait_desc, the reason the space can’t be reused:

Level 17 means the batch stops at the failing statement. A related error, 1105 (Could not allocate space for object … because the 'PRIMARY' filegroup is full), is the same problem in the data file.

Common causes

1. FULL recovery with no log backups. New databases copy the recovery model of model, which is FULL on a default install (and in Microsoft’s Docker images). Once the first full backup has run, the log keeps everything until a log backup, and grows until it hits a limit.
2. A transaction left open: a session that ran BEGIN TRANSACTION and never committed (an idle application connection, a query window left open), even in SIMPLE recovery.
3. One huge transaction: deleting or updating millions of rows in one statement, or an index rebuild, which needs log for all of it until it ends.
4. A capped log file or a full disk, with a MAXSIZE set low or autogrowth turned off.
5. A stalled availability group replica, replication or CDC.

How to fix it

Read the reason and the space used

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'<database>';

USE [<database>];
SELECT total_log_size_in_bytes / 1048576.0 AS log_mb, used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

DBCC SQLPERF(LOGSPACE) shows the same for every database at once.

LOG_BACKUP: back up the log

BACKUP LOG [<database>] TO DISK = N'<path>/<database>_log.trn';

That makes the space reusable straight away; then schedule regular log backups so it doesn’t fill again. If you don’t need point-in-time restores for this database, switch it to SIMPLE recovery instead, and the log is reused after each checkpoint:

ALTER DATABASE [<database>] SET RECOVERY SIMPLE;

That ends the chain of log backups: if you switch back to FULL later, take a full backup to start a new chain.

ACTIVE_TRANSACTION: find the transaction

DBCC OPENTRAN (N'<database>');

SELECT st.session_id, s.login_name, s.status, t.database_transaction_begin_time,
       t.database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions AS t
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
WHERE t.database_id = DB_ID(N'<database>')
ORDER BY t.database_transaction_begin_time;

Have its owner commit or roll it back, or end the session with KILL <session_id>, which rolls the transaction back (that takes a while for a big one). For large deletes and updates, work in batches of a few thousand rows, each its own transaction.

Give the log room

If the log is capped or the disk is full, raise the limit or add a second log file on another disk, then fix the reason above:

ALTER DATABASE [<database>] MODIFY FILE (NAME = N'<logical log name>', MAXSIZE = 64GB);

Shrinking the log file doesn’t help while it’s full: only space that can already be reused can be given back. Shrink once afterwards if it grew far beyond its normal size, not as a routine.

Azure SQL Database

Log backups, disk space and file growth are managed for you, so a lasting LOG_BACKUP there is a matter for Azure support. The usual cause is a long-running or forgotten transaction (ACTIVE_TRANSACTION), found and ended as above. A single transaction that writes too much log is ended with error 40552 (The session has been terminated because of excessive transaction log space usage.); split it into smaller transactions.

Reproduce it

On a temporary SQL Server 2022 container of our own (16.0.4295.3, removed afterwards), a database shop with a 4 MB log capped at 8 MB, set to FULL recovery, a full backup taken, then rows of 2,000 bytes inserted in a loop:

Msg 9002, Level 17, State 2, Server 54e64f9ad96b, Line 5
The transaction log for database 'shop' is full due to 'LOG_BACKUP' and the holdup lsn is (42:264:1).

The loop stopped after 1,901 rows. log_reuse_wait_desc was LOG_BACKUP, the log 8 MB and 100% used, and a further single insert failed the same way. After BACKUP LOG shop, the reason was NOTHING, 10.8% of the log was used, and the insert worked. Switched to SIMPLE recovery, with a second session holding a transaction open (one insert, then a 25-second wait), the loop failed with:

Msg 9002, Level 17, State 4, Server 54e64f9ad96b, Line 5
The transaction log for database 'shop' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is (53:616:5).

DBCC OPENTRAN named the other session (SPID (server process ID): 66) and its start time, and the query above showed it with 2,272 bytes of log: a tiny transaction can hold a whole log. A second database in FULL recovery with no full backup yet took all 20,000 rows in the same 8 MB log, with log_reuse_wait_desc NOTHING: until the first full backup, the log is reused as in SIMPLE. With the data file capped at 20 MB, the inserts failed with Msg 1105, Level 17, State 2 … Could not allocate space for object 'dbo.events'.'PK__events__3213E83FF7850BDF' in database 'shop' because the 'PRIMARY' filegroup is full …. The same LOG_BACKUP test on temporary 2019 and 2025 servers gave 9002 with level 17 and state 2; 2019’s message ended at 'LOG_BACKUP'. without the LSN.

In Inlet

Inlet’s Activity view lists sessions and who blocks whom, which helps find the session holding an old transaction, and can end it (KILL, Pro); it also shows tables’ sizes. It needs VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE from SQL Server 2022). Inlet doesn’t run SQL Server backups yet, so take the log backup with BACKUP LOG in a query tab or your usual backup job. When a write fails, Inlet shows the message with SQL Server error 9002, state 2, severity 17 and links to this page.