Troubleshoot SQL Error 9002- Transaction Log Full
Overview of SQL Error 9002
In previous articles we have discussed about temporary tables in SQL Server and know how to create them, Now in this article we will discuss how to fix SQL Server Error 9002, when the transaction logs are full in a log file. In SQL server when a log file is filled up from transaction logs, it shows SQL Error 9002. Generally the transaction logs are filled up when the SQL server database is online or if it is in recovery mode. In such situation if the transaction logs are filling continuously and the database is online, it remains online, but cannot be modernized.
If the transaction log file filled up at the time of recovery, the database is marked as a RESOURCE PENDING, by the database engine. In such situation, it is necessary to make log space available.
SQL Error MSG 9002 Specification:
The SQL Server shows Severity Level 19
Required Actions for the Error 9002:
The following user actions are required for the SQL Server Error 9002.
- Create a backup and truncate the transaction logs.
- Make the log space available.
- Move log file to another disk drive.
- Increase the size of log file.
- Add another log file on a separate disk.
The above actions are discussed below:
1. Create a backup and truncate the transaction logs.
If your database is in full or bulk-logged recovery model, and if the transaction log is not backed up, you must need to take the backup of your transaction logs and allow Database Engine to truncate the transaction logs to the point of the last backup. This will frees some space for new transactions.
2. Make the disk space available
You need to make the disk space available by deleting or moving some other files on which the transaction log file is contained.
3. Move log file to another disk drive
If you are not able to make the disk space available for the drive on which your transaction log file is contained, try to move the log file on another disk with available space. If you are performing this action:
Ensure that the other disk has sufficient space for new transaction logs.
- To accomplish this action you need to detach the database by sp_detach_db.
This action makes the log file unavailable as long as you do not re-attach it.
- For reattaching the database, you can execute the sp_attach_db.
4. Increase the size of log file.
If you have enough space on the log disk, then you can increase its size. You can increase its maximum size up to two terabytes (TB) per log file.
If the database is online and the sufficient disk space is available, to increase the size of the log file, you can either:
- Produce a single growth increment
- Enable autogrow by the ALTER DATABASE statement
5. Add or enlarge the log file
You can also add an additional log file for the database and gain more space.
- To add another log file on a separate disk, use ALTER DATABASE ADD LOG FILE.
- Use MODIFY FILE clause of the ALTER DATABASE to enlarge the log file.
The above-mentioned actions help you to fix SQL Server error 9002. However, if the above workaround doesn’t troubleshoot your problem then you can try SQL Server transaction log reader to Analyze SQL Server log file.