Are you trying to fix SQL Server Error 9002?
We can help you.
Sometimes in SQL server, when a log file gets filled up with transaction logs, it shows SQL Server Error 9002.
Usually, the transaction logs get filled when the SQL server database is online or if it is in recovery mode.
Therefore, SQL Server Error 9002 indicates that the SQL Transaction Log file is full or the database is running out of space. This makes the transaction log file to increase until the log file utilizes all the available space in disk. When it cannot expand any more, you become unable to perform any modification operations on the database.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve related SQL Server Errors.
In this context, we shall look into method to fix SQL Server Error 9002 when the transaction logs are full in a log file.
How to resolve SQL Server Error 9002 ?
We can do the following steps to fix this error:
1. First create a backup and truncate the transaction logs
2. Make the log space available
3. Move log file to another disk drive
4. After that increase the size of the log file
5. Add another log file on a separate disk
6. Finally complete or kill a long-running transaction
1. Create a backup and truncate the transaction logs
If our database is in full or bulk-logged recovery model and if the transaction log is not backed up, we must take the backup of 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
We can do this by deleting or moving some other files on which the transaction log file is contained.
3. Move log file to another disk drive
If we are not able to make the disk space available for the drive on which our transaction log file is contained, we can try to move the log file on another disk with available space.
We must keep in mind the following points while doing this:
i. We have to ensure that the other disk has enough space for new transaction logs.
ii. We can detach the database by using the following command:
sp_detach_dbiii. This action will make the log file unavailable as long as we do not re-attach it.
iv. For reattaching the database, we can execute the following:
sp_attach_db4. Increase the size of the log file
If we have enough space on the log disk, then we can increase its size.
To increase the size of the log file, we can either do any of the following:
i. Produce a single growth increment
ii. Enable autogrow by the ALTER DATABASE statement
5. Add or enlarge the log file
We can also add an additional log file for the database and gain more space.
i. To add another log file on a separate disk, use ALTER DATABASE ADD LOG FILE.
ii. Use MODIFY FILE clause of the ALTER DATABASE to enlarge the log file.
6. Complete or kill a long-running transaction
i. Discovering long-running transactions
A very long-running transaction can cause the transaction log to fill.
For checking long-running transactions, we can use one of the following:
1.sys.dm_tran_database_transactionsor
2. DBCC OPENTRANii. Kill a transaction
It will be better to end the process if it's taking too long.
We can use the KILL statement for doing this.
[Need assistance in fixing SQL errors? We can help you. ]
Conclusion
This article covers methods to resolve SQL Server Error 9002 for our customers. SQL Server Error 9002 happens when the SQL Transaction Log file becomes full or indicated the database is running out of space. A transaction log file increases until the log file utilizes all the available space in disk. A quick fix is to create more space for log file.
To Fix SQL Server Error 9002:
1. Backup Transaction Log File
Incase, SQL database that you are using is full or out of space, you should free the space. For this purpose, it is needed to create a backup of transaction log file immediately. Once the backup is created, the transaction log is truncated. If you do not take back up of log files, you can also use full or Bulk-Logged Recovery model to simple recovery model.
2. Free Disk Space
Generally, the transaction Log file is saved on the disk drive. So, you can free the disk space which contains Log file by deleting or moving other files on order to create some new space in the drive. The free space on disk will allow users to perform other task and resolve SQL Error Log 9002 The Transaction Log for Database is Full.
3. Move Log File to a Different Disk
If you are not able to free the space on a disk drive, then another option is to transfer the log file into a different disk. Make sure another disk in which you are going to transfer your log file, has enough space.
i. Execute sp_detach_db command to detach the database.
ii. Transfer the transaction log files to another disk.
iii. Now, attach the SQL database by running sp_attach_db command.
4. Enlarge Log File
If the sufficient space is available on the disk then you should increase the size of your log file. Because, the maximum size for a log file is considered as 2 TB per .ldf file.
To enlarge log file, there is an Autogrow option but if it is disabled, then you need to manually increase the log file size.
i. To increase log file size, you need to use the MODIFY FILE clause in ALTER DATABASE statement. Then define the particular SIZE and MAXSIZE.
ii. You can also add the log file to the specific SQL database. For this, use ADD FILE clause in ALTER DATABASE statement.
Then, add an additional .ldf file which allows to increase the log file.
This article covers methods to resolve SQL Server Error 9002 for our customers. SQL Server Error 9002 happens when the SQL Transaction Log file becomes full or indicated the database is running out of space. A transaction log file increases until the log file utilizes all the available space in disk. A quick fix is to create more space for log file.
To Fix SQL Server Error 9002:
1. Backup Transaction Log File
Incase, SQL database that you are using is full or out of space, you should free the space. For this purpose, it is needed to create a backup of transaction log file immediately. Once the backup is created, the transaction log is truncated. If you do not take back up of log files, you can also use full or Bulk-Logged Recovery model to simple recovery model.
2. Free Disk Space
Generally, the transaction Log file is saved on the disk drive. So, you can free the disk space which contains Log file by deleting or moving other files on order to create some new space in the drive. The free space on disk will allow users to perform other task and resolve SQL Error Log 9002 The Transaction Log for Database is Full.
3. Move Log File to a Different Disk
If you are not able to free the space on a disk drive, then another option is to transfer the log file into a different disk. Make sure another disk in which you are going to transfer your log file, has enough space.
i. Execute sp_detach_db command to detach the database.
ii. Transfer the transaction log files to another disk.
iii. Now, attach the SQL database by running sp_attach_db command.
4. Enlarge Log File
If the sufficient space is available on the disk then you should increase the size of your log file. Because, the maximum size for a log file is considered as 2 TB per .ldf file.
To enlarge log file, there is an Autogrow option but if it is disabled, then you need to manually increase the log file size.
i. To increase log file size, you need to use the MODIFY FILE clause in ALTER DATABASE statement. Then define the particular SIZE and MAXSIZE.
ii. You can also add the log file to the specific SQL database. For this, use ADD FILE clause in ALTER DATABASE statement.
Then, add an additional .ldf file which allows to increase the log file.
