Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
Wondering how to fix SQL Server Error 9002? We can help you.
Often 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.
Here at Bobcares, we often get requests from our customers to fix similar errors as a part of our Server Management Services.
Today let’s see how our Support Engineers fix SQL Server Error 9002 when the transaction logs are full in a log file.
How to fix SQL Server Error 9002: The transaction log for database is full
A typical Error 9002 will look like the one given below:
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:
1. We have to ensure that the other disk has enough space for new transaction logs.
2. We can detach the database by using the following command:
sp_detach_db
3. This action will make the log file unavailable as long as we do not re-attach it.
4. For reattaching the database, we can execute the following:
sp_attach_db
4. 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:
1. Produce a single growth increment
2. 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.
1. To add another log file on a separate disk, use ALTER DATABASE ADD LOG FILE.
2. Use MODIFY FILE clause of the ALTER DATABASE to enlarge the log file.
6. Complete or kill a long-running transaction
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_transactions
or
2. DBCC OPENTRAN
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? We can help you]
Conclusion
In short, we saw how to fix SQL Server Error 9002, also we saw the steps that our Support Engineers follow to fix this issue for our customers.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
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:
- 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.
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.
Problem:SQL Error Log 9002 The Transaction log for database is full
In the recent version of, SQL Server error 9002 is shown as:
The transaction log for database ‘%ls’ is full due to ‘%ls’.
In the previous version of SQL Server, error code 9002 is not very informative. It looks like:
The transaction log for database ‘Database_Name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
SQL Error Log 9002 The Transaction Log for Database is Full – Reasons
SQL Server Error 9002 arises 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. When it cannot expand any more, you become unable to perform any modification operations on the database.
However, it is difficult to know the reasons for filling the SQL log file. Even, if you ask any database administrator to cause of running transaction log out of space, probably they will not be able to answer your question. Because there can be plenty of reasons for SQL Error 9002 problem and also different workarounds for each situation. In fact, if the database is Online and the LOG file get fills then, user can only read the table and unable to do any modification in SQL database. On the other hand, if the log file space filled during the recovery task, then Database Engine put the database on Resource Pending mode. All-in-all, there is a need to create more space for log file.
Also Read :- Guide to View Log File of SQL Server
Quick Glance at Transaction Log File
Every SQL database consists of two files, a .mdf file and .ldf file. The .mdf file is the primary data file and .ldf file a transaction log file that contains all information about the previous operations performed on SQL database. If you added, deleted or done any modification on a SQL database, these are written to the log file. It helps SQL administrator at the time of recovery or finding any dreadful activity on the SQL database like who deleted data from table in SQL Server. With the help of the last modifications implemented within the database, it allows the database to roll back or restore transactions in the event of either an application error or hardware failure.
How to Fix SQL Server Error 9002?
It is evident from above that ‘SQL Error Log 9002 The Transaction Log for Database is Full’ has lots of consequences. So, it is required to resolve this error in Microsoft SQL Server. There are various solutions available, you can choose any of them according to your situation and resolve SQL Error 9002 transaction log full glitch.
Solution #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.
Also Read : How to Recover Data From SQL Log File?
Solution #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.
Solution #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.
-
Execute
sp_detach_db command to detach the database. -
Transfer
the transaction log files to another disk. -
Now,
attach the SQL database by running sp_attach_db command.
Solution #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.
-
To
increase log file size, you need to use the MODIFY FILE clause in
ALTER DATABASE statement. Then define the particular SIZE and
MAXSIZE.
- 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.
That’s all about how to resolve SQL Server Log 9002 error. In such a situation, the error is not fix by the above methods, then you can use try SQL Log Analyzer Software. It is a utility that scan a log file in-detailed manner of any size and recover the records – INSERT, UPDATE & DELETE into the SQL Server or SQL Server Compatible SQL Scripts.
Wrapping Up
In this post, we have discussed the – SQL Error Log 9002 the transaction log for database is full ,which encounters due to overfilling of transactions in a log file. We have discussed various workarounds in this write-up that will help users to resolve this SQL Error 9002 transaction log problem.
Frequently Asked Questions –
Q – How to Know what prevents log truncation due to which transaction log size increases ?
A – To get information about what is preventing log truncation, try log_reuse_wait & log_reuse_wait_desc.
Q – What should I do when error 9002 occurs because of a very long-running transaction?
A – First identify the transaction and then commit it rather than rolling it back.
Q – My disk space where log file is saved gets filled completely. What should I do now to fix SQL Server 9002 error ?
A – If you do not have enough disk space, then move the log file to a different drive which has appropriate space.
How to clear the SQL Server transaction log and fix the error 9002?
It is time to clear the transaction log when a SQL Server database is slow, or the available disk space is getting abnormally small. Indeed, it is sometimes enough to clear the SQL Server transaction log and reduce the files in the SQL Server database. Often, during this operation, this message appears : The database transaction log is full. Or Check the log_reuse_wait column of sys.databases.
Before emptying the logs, inspect the data in the sys.databases system view of the database and locate the table that is not in the NOTHING state. To avoid the error “The database transaction log is full” and to shrink the files, it is necessary to execute two commands, shrink database and then shrink file. Indeed the full detailed error message looks like this one:
Microsoft SQL Server Error 9002 : The transaction log for database YOUR-DB is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc COLUMN IN SYS.DATABASES (#9002)
Run SHRINKDATABASE to clear the transaction log
In addition, if trying to run a DBCC SHRINKDATABASE script like the one below. So, run the SHRINKDATABASE command and replace DATABASE_EXAMPLE with the name of the database file. The command will reduce the size of the database on the disk. Adapt the path with the file, stored in a folder of this type, ideally on a dedicated disk: D:\SQL_DATA\DBFILE\
USE DATABASE_EXAMPLE; DBCC SHRINKDATABASE(N'DATABASE_EXAMPLE', 10, TRUNCATEONLY);
In my example, by default the database name looks like this DATABASE_EXAMPLE.mdf. Note that a SQL Server database main file is a MDF file for Master Database File.
Run SHRINKFILE to reduce the database files size
Then use the SHRINKFILE command to replace DATABASE_EXAMPLE with the name of the SQL Server log file, for example in a dedicated folder like this one D:\SQL_DATA\DB\DBLOG\. In my example, by default the log file name is like this DATABASE_EXAMPLE_log.ldf. It’s a LDF file, for Log Database File.
ALTER DATABASE DWH SET RECOVERY SIMPLE;
DBCC SHRINKFILE ('DATABASE_EXAMPLE_log', 1);
ALTER DATABASE DATABASE_EXAMPLE SET RECOVERY FULL;
If an error of this type occurs when running one of the commands:
The database transaction log is full. Check the log_reuse_wait_desc column in sys.databases.
Then check the active transactions from the log_reuse_wait column. To do so, run this query in SQL Server Management Studio.
SELECT [name] ,[database_id] ,[source_database_id] ,[owner_sid] ,[create_date] ,[compatibility_level] ,[collation_name] ,[user_access] ,[user_access_desc] ,[is_read_only] ,[is_auto_close_on] ,[is_auto_shrink_on] ,[state] ,[state_desc] ,[is_in_standby] ,[is_cleanly_shutdown] ,[is_supplemental_logging_enabled] ,[snapshot_isolation_state] ,[snapshot_isolation_state_desc] ,[is_read_committed_snapshot_on] ,[recovery_model] ,[recovery_model_desc] ,[page_verify_option] ,[page_verify_option_desc] ,[is_auto_create_stats_on] ,[is_auto_update_stats_on] ,[is_auto_update_stats_async_on] ,[is_ansi_null_default_on] ,[is_ansi_nulls_on] ,[is_ansi_padding_on] ,[is_ansi_warnings_on] ,[is_arithabort_on] ,[is_concat_null_yields_null_on] ,[is_numeric_roundabort_on] ,[is_quoted_identifier_on] ,[is_recursive_triggers_on] ,[is_cursor_close_on_commit_on] ,[is_local_cursor_default] ,[is_fulltext_enabled] ,[is_trustworthy_on] ,[is_db_chaining_on] ,[is_parameterization_forced] ,[is_master_key_encrypted_by_server] ,[is_published] ,[is_subscribed] ,[is_merge_published] ,[is_distributor] ,[is_sync_with_backup] ,[service_broker_guid] ,[is_broker_enabled] ,[log_reuse_wait] ,[log_reuse_wait_desc] ,[is_date_correlation_on] ,[is_cdc_enabled] ,[is_encrypted] ,[is_honor_broker_priority_on] FROM [tempdb].[sys].[databases]
Then inspect the databases corresponding to the rows with the status ACTIVE_TRANSACTION in the log_reuse_wait_desc column. Furthermore, it is necessary to run the SHRINKFILE and SHRINKDATABASE script on the database(s) in question to unblock the status and return it to NOTHING.
Finally, after emptying the SQL Server transaction log, it is possible to reduce the database without any problem. Thus, this article shows the procedure to completely delete a useless SQL Server database.
Reducing logs is not always a good idea for performance. Indeed, the relational database management system, or RDBMS, fragment the data after the database reduction. And when rebuilding the indexes, they will take up all the necessary space. That’s it for this post on how to fix the common SQL Server 9002 error, well known by DBAs.
How to clear the transaction log with SQL Server ?
To clear the SQL Server transaction log and avoid error 9002, use the commands DBCC SHRINKDATABASE and DBCC SHRINKFILE. To reduce the database and the associated files respectively.