SQL Server Error 9002 – Full Transaction Log

A full transaction log can occur for a number of reasons. Typically it is due to the log not being truncated with regular transaction log backups, or something else not allowing the log to truncate. I often find Change Data Capture enabled and the jobs not running, SQL Server Replication in place and replication not working, or the database is in an Availability Group with data movement suspended.

When I find a full transaction log, that is usually due to the disk finally running out of space. Ever so often, I find that the transaction log has a fixed size limit on the size of the file and it has now hit that limit. While it is very possible, I can’t say that I’ve ever run into a situation where auto growth was disabled for a transaction log file, but that is one sure way to hit this error message as well.

You’ve encountered this error, what can you do?

The first thing that you need to do is truncate the transaction log. If transaction log backups have not been being taken, you can back up the transaction log which should then truncate the log and allow you to shrink it.

If I find a database that hasn’t had a transaction log backup in a really long time, several months for example, I’ll switch recovery models from FULL to SIMPLE, then back to FULL in order to truncate the log and shrink it. This is a quick action you can take if the transaction log being full is causing a production outage. It is the quickest route to getting users back online.

If a database is participating in an Availability Group, you can’t switch the recovery model. What I usually have to do in that situation if resuming database movement does not work, is to remove the database from the availability group, cleanup the transaction log, then add the database back to the Availability Group.

When backing up the transaction log and shrinking does not work, something is still preventing the log from truncating. I query sys.databases to view the log_reuse_wait_desc and log_reuse_wait to see what could be preventing log truncation. Here is an old article that covers what the log_reuse_wait_desc mean. Just be aware that ‘replication’ can be change data capture or SQL Server Replication.

If you are having to shrink the transaction log, please check for VLF count (virtual log files) as well. You may need to clean those up too. The only known method I know to reset VLFs is to backup the log, shrink the log file, and then repeat a few more times.