Fix Error 9002: Transaction Log Full For SQL Server

Overview of SQL Server Error 9002

Sometimes while working on SQL Server database we come across an error 9002. Which indicates that Transaction log for SQL Server is full. There may be several reasons through which the log file may become very large and run out of space or become full. Transaction log error 9002 generally occur when a log file is full or the disk space in which log file stored is full and cannot expand the log files further. In such circumstances, database remains online, but can only be read and no update operation can be performed. If this error occur while recovery then databases marked as resource pending.

Error 9002 msg:

The log file for database '%*Is' is full. Back up the transaction log for the database to free up some log space.

SQL Server database records all the transactions and the modifications done by each transaction. Transaction log must be regularly truncated to maintain the log space and keep it away from filling up. Some operations can be minimally logged to reduce their impact on transaction log size.
If the error 9002 occurred, when database was in recovery mode then after resolving problem ALTER DATABASE database_name SET ONLINE must be used.

How to Fix SQL Server Error 9002?

Following action can be performed for troubleshooting SQL Server transaction log full Error 9002:

• Log backup can be done.
• Log file can be moved to some other disk having sufficient space.
• Log file size can be increased.
• Freeing disk space so that the log file may grow automatically.
• Long running transactions could be killed.
• Adding a log file to other disk.

However, Transaction log is an essential part of database, which is required to return the database in consistent state if any system failure occurred. That’s why shrinking, deleting or moving transaction log be done after fully understanding the outcome of the action performed.
The above actions performed for troubleshooting t-sql error 9002 described below:

Transaction Log Backup

In case database uses the full or bulk-logged recovery model, and transaction log backup has not been done recently. Then there is need to take recent backup of the transaction log to free some space and supports restoring the database from a specific point. Log backup should be taken frequently to keep the log from filling up again.

Moving Log File to Another Disk

If creating enough space on the disk that containing the log file is not possible, then log file should be moved to some other disk having sufficient space. While moving log file one should never place the log file on a compressed file system. The log file can be moved by using concept of database detach and attach.
sp_detach_db executed to detach database.
sp_attach_db executed to attach database.

Increase Log File Size

As maximum size for log file is two terabytes(TB). So if space available on the disk log file size can be increased. Size can be increased manually to produce a single grow increment in case autogrow disable and a database is online. Also, we can enable autogrow by using ALTER DATABASE statement.

Freeing Disk Space

The disk containing the transaction log file can be freed by deleting or moving some files from that disk to another disk. Freeing the disk space will enlarge the log file automatically on that disc.

Add Log file to Another Disk

A new log file can be added to the database on the different disk by using ALTER DATABASE ADD LOG FILE.

Conclusion

We get to know about SQL Server Transaction Log Error 9002. Error 9002 in SQL Server occurred when the transaction log file is full. While moving or deleting a transaction log file the outcome of the action performed should be kept in mind. Several actions which can be performed to solve this error are discussed briefly.

Suggested Reading

• SQL Backup Repair Tool to Recover Corrupt BAK File (Review)
• SQL Server Schema Corruption Error 211: A Technical Solution