“I am having a long-running process that holds a transaction for the full duration and I have no control over the way this is executing. It is so because the transaction is held open for the full duration and the SQL Server cannot increase the size of log file. Hence, the procedure becomes fail and throwing an error message ‘The transaction log for database ‘xyz’ is full’. I have tried to prevent this by increasing the size of the transaction log file in the database properties however, I get the same error. I have no idea that what should I try next. Any ideas how to fix SQL Server Error 9002?”
SQL Server Error 9002: A Brief Overview
There comes sometimes while working with SQL Server database, users encounter an error code 9002. The error indicates that the transaction log file is filled up with transaction logs. Usually, the transaction logs are filled up when the SQL Server database is in online mode or recovery mode. In such scenarios, if the database transactions are filling continuously and the database is in online mode then, it will remain online and cannot be modernized. In addition, if the transaction log is filled up while performing recovery then, it will be marked as the RESOURCE PENDING by the database engine. Hence, it is essential to make log space available.
SQL Server displays Severity Level 19
Best Way to View Transactions Activities in SQL Server
LDF File Viewer is a reliable FreeViewer utility to analyze and read all the transactions such as Insert, Update, Delete etc., of SQL Server. With this, users can take a preview of the data from the Live SQL Database.
Techniques to Fix SQL Server Error 9002
When the transaction log for database gets full, the database remains online but, users cannot update or read it. Hence, it is essential to fix this error message and there are multiple ways to resolve this error.
Option 1. Create a backup and truncate transaction logs
If the SQL Server database that you are using is out of space or bulk-logged recovery model then, you have to free up the storage space. In order to do this, you need to free up storage space. First, create a backup of the transaction log file and after this, truncate the transaction log. If you do not want to take the backup of log files then, you can also use Bulk-Logged or full model for simple recovery. It will free up storage space for new transactions.
Option 2. Free up disk storage space
Basically, all the transaction log files are saved on the disk drive. Thus, you can make space for transactions via freeing up the disk space and this can be done by deleting some unnecessary files. After this, users can perform various transactions on the SQL Server database.
Option 3. Shift log files to another disk
Sometimes users become unable to make free storage space on the disk. Well, in this case, one can transfer the transaction log file into another disk. However, before moving the transaction log file to another disk, make sure that another disk has enough storage space. Below are the steps to move the log file:
- Run sp_detach_db command for detaching the database
- After that, move the transaction log files into another disk
- Execute sp_attach_db command to attach the SQL database
Option 4. Enlarge the size of log file
If your disk has sufficient storage space then, you can maximize the size of the log file. It is so because the maximum log file size is considered as 2 TB each .ldf file. In order to increase the size of log files, one can use Autogrow option but, if it is disabled then, the size can be increased manually. Below are the guidelines for the same:
- You have to utilize the MODIFY FILE clause in the ALTER DATABASE statement and define the specific SIZE and MAXSIZE
- You can also add another transaction log file to a specific SQL database. To do this, use ADD FILE clause in ALTER DATABASE statement. After that, add an additional .ldf file that lets to enlarge the size of the log file.
In this technical blog, we have discussed SQL Server error 9002: the transaction log for database is full. The error basically occurs when the transaction log file runs out of storage space. Here we have disclosed different techniques by which one can fix this issue without any difficulty.