Troubleshoot SQL Error 9002- Transaction Log Full

Table of Content

• An Overview
• Specification
• Conclusion

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. The transaction log for database is full due to replication & other causes as well that we are going discuss further.

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 here.

Error Message:

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 here below in depth to counter the error 9002 SQL server database:

1. Create a backup and truncate the transaction logs.

If your database is in full or bulk-log recovery model, and if there isn’t any backup for the transaction log, 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 free 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 the location of the transaction log files. In some cases, the transaction log for database is full due to replication of data. Therefore, remove all the duplicate data files.

3. Move log file to another disk drive

If you are not able to make the disk space available for the drive on the location of the log file, 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:

1. Produce a single growth increment
2. 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.

Also Read: How to View MDF File Without SQL Server

Conclusion

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. The transaction log for database msdb is full issue is difficult to solve without the right method. However, one single mistake can be a severe problem.