Overview of SQL Server Error 9002Sometimes 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.
The above actions performed for troubleshooting t-sql error 9002 described below:
Transaction Log BackupIn 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 DiskIf 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.