Introduction to SQL Server Error 9002
"SQL Server Error 9002 transaction log full" occurs when the SQL Server transaction log file for the indicated database run out of space. In such situation the SQL database remains online but it can only be read and you cannot update it. If the log files are under recovery mode, then the database engine marks the database as RESOURCE PENDING. In both case, user action is required to make log space available.
Error Message:
The log file for database '%*Is' is full. Back up the transaction log for the database to free up some log space.
Troubleshoot a Full Transaction Log (SQL Server Error 9002)
The user action to fix error code 9002 depends on the situation. Possible actions to fix the error are given below:
- Backing up the transaction log & freeing the disk space
- Moving the log file to another disk drive having sufficient space
- Adding or enlarging the size of a log file
These actions are briefly discussed below. You should follow them by considering what caused the transaction log to fill. Possible causes include a long running transaction or a published transaction.
- A long-running transaction prevents truncation and reclamation of transaction log space, which normally happens either automatically (under the Simple Recovery model) or as a result of taking a log backup (under the Full Or Bulk-Logged Recovery model). You may have to use the KILL statement. Use KILL very carefully, especially when critical processes are running.
- If replication is turned on for the database and has fallen behind, a published transaction that has not been passed into the distribution database may be preventing log truncation.
- Backup the Transaction Log
- Move the Log File to a Disk Drive with Sufficient Space
- Adding or Enlarging a Log File
Suppose the database that you are using is in Full or Bulk-Logged Recovery model, then you should back up the transaction log immediately to free the space. You should start taking log backups, if you are not taking log backups. Otherwise, you can also switch from Full or Bulk-Logged Recovery model to Simple Recovery model.
Free Disk Space
You can free disk space of the disk drive that contains the transaction log file for the database. Freeing disk space allows the recovery system to enlarge the log file automatically.
If you cannot free sufficient disk space on the drive that currently contains the log file, then move the file to another drive with sufficient space.
> Ensure that the other drive has sufficient free space for the transaction log, detach the database by executing sp_detach_db. Detaching a database makes it unavailable until it is reattached.
> Move the transaction log files to the other drive.
> Attach the database by executing sp_attach_db.
Alternatively, you can gain space by adding an additional log file for the database or enlarging the existing log file (if disk space permits).
> To add a log file to the specified database, use the ADD FILE clause in the ALTER DATABASE statement. Adding an additional log file allows the existing log to grow.
> To enlarge the log file, use the MODIFY FILE clause in the ALTER DATABASE statement, specifying the SIZE and MAXSIZE.
Conclusion
In the present article we have discussed about SQL Server Error code 9002 transaction log full. Here, we have also discussed the techniques to troubleshoot a full transaction log (sql server error 9002).
EmoticonEmoticon