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

Certain SQL Server errors, such as SQL Error 9002, can be a challenge for database administrators. This error states that the transaction log for the database is full due to replication and is a critical issue. Users without technical knowledge often get troubled by the transaction log for the msdb is full issue.

Through this article, we are going to help users with a complete solution for this error. The guide not only explains the manual approaches, but also addresses the limitations of these approaches and suggests the professional way of resolving the error.

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

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

In recent versions of SQL Server, error 9002 SQL Server is displayed as:

The transaction log for database ‘%ls’ is full due to ‘%ls’.

In previous versions of SQL Error 9002 was not that informative, making it difficult for users to understand:

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.

If we take a very specific example of how the SQL Server error 9002 message looks, here is the display message that pops up when the error occurs:

error: array ( [0] => array ( [0] => 42000 [sqlstate] => 42000 [1] => 9002 [code] => 9002 [2] => [microsoft][odbc driver 17 for sql server][sql server]el registro de transacciones de la base de datos 'arbec' está lleno debido a 'log_backup'. [message] => [microsoft][odbc driver 17 for sql server][sql server]el registro de transacciones de la base de datos 'arbec' está lleno debido a 'log_backup'. ) )

Several Variants for SQL Error 9002  

After learning the possible reasons, it is also important for users to know about the common variants of the error they might encounter. Here are some common error variations encountered by database administrators. The transaction log for database ‘%ls’ is full due to ‘%ls, where %ls accounts for the following in SQL Server error 9002:

• Due to no log backup taken – LOG_BACKUP
• Because of long running transactions in SQL Server – ACTIVE_TRANSACTION
• If the checkpoint isn’t completed – CHECKPOINT
• If there is a replication delay – REPLICATION
• Due to Backup or restore process being in progress – ACTIVE_BACKUP_OR_RESTORE

These are some of the common reasons that can be responsible for the occurrence of the SQL Error 9002 and can further create bigger challenges for the users.

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 is a transaction log file that contains all information about the previous operations performed on SQL database. If you added, deleted, or made any modifications to a SQL database, these are written to the log file. It helps SQL administrators during restoration or when 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? Troubleshooting Steps Explained

It is evident from above that ‘SQL Error Log 9002 The Transaction Log for Database is Full’ has lots of consequences. So, it is necessary 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 issue.

Step 1. Backup Transaction Log File & Truncate

If the SQL database that you are using is full or out of space, you should free up space. For this purpose, it is necessary to create a backup of the transaction log file immediately for SQL Server error 9002 resolution. Once the backup is created, the transaction log is truncated. If you do not back up the log files, you can also use the FULL or Bulk-Logged to the SIMPLE recovery model.

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

Step 2. Free Disk Space for Additional Data to Fix SQL Error 9002

Generally, the transaction Log file is saved on the disk drive. So, you can free the disk space that contains the log file by deleting or moving other files in order to create some new space on the drive. The free space on the disk will allow users to perform other tasks and resolve SQL Error Log 9002 the transaction log for database msdb is full. 

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

If you are not able to free space on a disk drive to fix SQL Server Error 9002, then another option is to transfer the log file to a different disk. Make sure the other disk to which you are going to transfer your log file has enough space.

1. Execute the sp_detach_db command to detach the database.
2. Transfer the transaction log files to another disk to resolve SQL Error 9002.
3. Now, attach the SQL database by running the sp_attach_db command.

Step 4. Enlarge Log File & Kill Long-Running Transaction

If sufficient space is available on the disk, then you should increase the size of your log file. The maximum size for a log file is 2 TB per .ldf file. To enlarge log file, there is an Autogrowth option, but if it is disabled, then you need to manually increase the log file size to repair the SQL Server error 9002.

• To increase the log file size, you need to use the MODIFY FILE clause in the 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 the ADD FILE clause in the ALTER DATABASE statement. Then, add an additional .ldf file, which allows you to increase the log file. This is an easier way to fix the transaction log for a database that is full due to a replication error.

T-SQL Method to Fix SQL Server Error 9002

In case the transaction log for the database is full due to the ‘log_backup’ error 9002 in SQL Server caused by 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 see 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 replication. Therefore, we had to cross-verify the effects, so we needed 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 SQL Error Log 9002 the transaction log for database msdb is full, which occurs 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 Server error 9002 error transaction log problem.

Also Read: SQL Server Error 823 Solution With Software

Frequently Asked Questions –