Troubleshoot SQL Error Log 9002 The Transaction Log for Database is Full

Nowadays, users are getting aware of the SQL Server technicalities day by day. However, there are still some issues left that annoy users. For example, the SQL Server error 9002 which states the transaction log for database is full due to replication is a critical one. Users without technical knowledge often get troubled by this.

Although, this article is going to provide users with a complete solution for this problem including T-SQL methods. Moreover, there are several cases, where the error arises due to damages in the Log file, so the solution for that is also mentioned here.

Overview to SQL Error 9002 – The Transaction Log for Database MSDB is Full

Problem:SQL Error Log 9002 The Transaction log for database is full

In the recent version of, error 9002 SQL Server 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

Error 9002 in SQL Server 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 restoration 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 without Hassles

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 restoration or finding any dreadful activity on the SQL database like checking 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 The Easy Way?

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.

Step 1. Backup Transaction Log File & Truncate

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 to simple recovery model.

Also Read : How to Recover Data From SQL Log File in Simple Steps?

Step 2. Free Disk Space for Additional Data

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.

Step 3. Move Log File to a Different Disk/Drive

If you are not able to free the space on a disk drive to fix SQL Server Error 9002, 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.

Step 4. Enlarge Log File & Kill Long Running Transaction

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. This is an easier way to fix the transaction log for database is full due to replication error.

T-SQL Method to Fix SQL Server Error 9002

In case, the the transaction log for database is full due to ‘log_backup’ error 9002 occurs due to replication, the procedure can be a bit different. here, we would execute the first query like this:

SELECT name, log_reuse_wait_desc
FROM sys.databases
where name = 'DB_Name'

Now, Under the log_reuse_wait_desc in sys.databases catalog view, users can witness several values like:

• NOTHING
• LOG_SCAN
• CHECKPOINT
• LOG_BACKUP
• REPLICATION
• OLDEST_PAGE
• ACTIVE_TRANSACTION
• AVAILABILITY_REPLICA
• DATABASE_MIRRORING
• ACTIVE_BACKUP_OR_RESTORE
• DATABASE_SNAPSHOT_CREATION

Out of all these, our case is of the replication. Therefore, we had to cross-verify the effects due to which we need to execute the following command:

SELECT [is_published]
,[is_subscribed]
,[is_cdc_enabled]
FROM sys.databases
WHERE name = 'Database_Name'

After this, we got this message in our display: 1 Row Affected

Now, in order to fix this issue, we executed the following command:

DECLARE @ScriptToExecute VARCHAR(MAX);
SET @ScriptToExecute = '';
SELECT
@ScriptToExecute = @ScriptToExecute +
'USE ['+ d.name +']; CHECKPOINT; DBCC SHRINKFILE ('+f.name+');'
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
WHERE f.type = 1 AND d.database_id > 4
-- AND d.name = 'NameofDB'
SELECT @ScriptToExecute ScriptToExecute
EXEC (@ScriptToExecute)

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 error 9002 error transaction log problem.

Also Read: SQL Server Error 823 Solution With Software

Frequently Asked Questions –