Fixing the SQL Server Error 9002

When working with the SQL server on a day-to-day basis, you may come across the 9002 error. It usually indicates that the SQL Transaction File is now full and requires you to increase the size. When this error occurs, most of the long-running transactions will fail or become slower. In this article, learn how to fix the error.

Fixing the SQL Server Error 9002

Before you can fix this error, let’s look at the error message, which looks like this:

Code:

BizTalk Server Administration
This operation field while accessing at least one of the Message Box databases. Some results might be omitted. (Microsoft.BizTalk.Administration.SnapIn)

Additional Information:
The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’, (Microsoft SQL Server, Error:9002)

As you can see, the database ‘BizTalkMsgBoxDb’ is full.

Here are the steps you need to take to fix this issue:

Create a backup of transaction logs and truncate it

The first thing you need to do is take a backup of your transaction logs. This is especially true if it isn’t backed up already or your database is full. After taking the backup, you must truncate the transaction logs with Database Engine till you reach the size of your last backup.

Doing so will free up space where new transactions can take place.

Increase the disk space

You’d also have to create some disk space. You can do this by removing or moving some files where the transaction log file is located. Start with less important files and then make arrangement for important ones.

Move the log file from existing to another disk drive

In case you couldn’t create more disk space, consider moving the log files form the existing disk to another disk drive. This way, space will not be an issue.

But make sure that the other disk drive has enough space, otherwise moving the log file will be worthless. Furthermore, you need to detach the database using the following command:

Code:

sp_detach_db

Detaching the database will ensure log file remains unavailable until you re-attach it. Once completed, you can reattach the database using the following command:

Code:

sp_attach_db

Expand log file’s size

After you have enough space on your disk, it’s time to increase the size of log file. There are two options:

Produce a single growth increment
Allow the file to grow on its own (autogrow) by using the ALTER DATABASE statement

Add log files

Instead of expanding the log file’s size, you can add more log files instead to create more space. Here’s what you need to do:

Use ALTER DATABASE ADD LOG FILE to add another log file
Use ALTER DATABASE along with the MODIFY FILE clause to enlarge log file

Kill the long-running transactions

Lastly, you need to discover and terminate long-running transactions that might be bugging down the server. Use one of the following to discover these transactions:

Code:

sys.dm_tran_database_transactions
OR
DBCC OPENTRAN

Find the transactions that are taking too long and kill them using the KILL statement.

So that’s how you deal with the SQL Server Error 9002.

Fixing the SQL Server Error 9002

More resources from kumkumsharma

Share this resource