SQL Server error 9002: the transaction log is full

#The error

Msg 9002: The transaction log for database 'Sales' is full due to 'LOG_BACKUP'.

The text after "due to" tells you the reason. Confirm it:

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = 'Sales';

SELECT total_log_size_in_bytes/1048576 AS log_mb, used_log_space_in_percent
FROM sys.dm_db_log_space_usage;   -- run in the database

#Fix by reason

#LOG_BACKUP

BACKUP LOG Sales TO DISK = N'E:\Backup\Sales_log_emergency.trn' WITH COMPRESSION, STATS = 10;

If the backup drive is also full, back up to another drive or a URL. Don't switch to SIMPLE recovery unless the customer accepts losing point-in-time recovery until the next full backup.

#ACTIVE_TRANSACTION

DBCC OPENTRAN('Sales');

SELECT s.session_id, s.login_name, s.host_name, s.program_name, t.transaction_begin_time
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
ORDER BY t.transaction_begin_time;

Warning

KILL rolls back the transaction, and a rollback can take as long as the work did. Check with the owner first, then monitor with KILL <spid> WITH STATUSONLY.

#Buy time: add log space

ALTER DATABASE Sales MODIFY FILE (NAME = Sales_log, MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB);
-- or add a second log file on another drive (remove it later)
ALTER DATABASE Sales ADD LOG FILE (NAME = Sales_log2, FILENAME = 'F:\Log\Sales_log2.ldf', SIZE = 10GB);

#Afterwards: shrink only if needed

Shrink only if the log grew because of a one-off event, and leave it at a size that fits normal peaks:

USE Sales;
DBCC SHRINKFILE (Sales_log, 20480);   -- target in MB

Repeated shrink-and-grow causes fragmentation (too many VLFs) and slow recovery.

#Prevent it

• Log backups every 15 minutes (or less) for FULL recovery databases, with an alert when a backup is missed.
• Alert when log usage is above 70%.
• Break big data changes into batches (DELETE TOP (50000) in a loop).

Tip

SQL Server Agent alerts on error 9002 with a notification to the DBA team cost nothing to set up, and turn this into a page instead of a customer outage.