How to Resolve SQL Server Error 9002 – A Step-by-Step Guide

SQL Server automatically creates the transaction log file (.ldf) and the .mdf file, whenever you create a new database. The transaction log file records all the transactions and modifications you made to the database. This file helps in individual transaction recovery, incomplete transaction recovery, and other disaster recovery solutions, like Always on Availability Groups, log shipping, and database mirroring.

Sometimes, the space in transaction log file fills up. Due to this, you may face various errors and issues while restoring backup or accessing the database. The SQL Server error 9002 is one such error that can occur when there is no space left in the log file to record new entries. The error usually occurs when you try to modify your database, specifically when executing the UPDATE, INSERT, DELETE, and RESTORE queries. The SQL Server error 9002 may appear with different error messages (see the below examples):

When this error occurs, you may fail to restore the backup file or update the records in the database. It stops the transaction growth. When the transaction log fails to grow in the primary replicas, it impacts the recovery process of database in the Always on Availability group setup. It happens because the secondary replicas in the availability group depend on the transaction log to sync with the primary replicas.

Let’s learn how to fix the SQL Server error 9002. Before proceeding, first understand the possible causes behind this error.

Reasons behind SQL Server Error 9002

The error 9002 in SQL Server usually occurs when the space in transaction log file fills up. This can happen due to various reasons, such as:

• You might have specified the log size to a fixed maximum value.
• Synchronization is not complete in Replication or an availability group.
• The Autogrowth option is disabled.
• The transaction log file reached its maximum specified size.
• Operations such as a checkpoint, restore, backup, log backup, replications, etc. in SQL Server are in progress.
• Open transactions.
• A long running transactions.
• Log not being truncated.

Troubleshooting Methods to Resolve SQL Server Error 9002

When you experience this error, first run the sys.databases (Transact-SQL) query using the log_reuse_wait_desc columns to find the exact cause why your transaction log file is full.

The command’s output will display one of the following values:

• ACTIVE_TRANSACTION
• CHECKPOINT
• REPLICATION
• ACTIVE_BACKUP_RESTORE
• DATABASE_SNAPSHOT_CREATION
• OLDEST_PAGE
• LOG_BACKUP
• NOTHING
• AVAILABILITY_REPLICA
• DATABASE_MIRRORING

If the above command displays the ACTIVE_TRANSACTION value, you can run the DBCC SQLPERF (Logspace) command to know the transaction log space usage data for all databases, including the current log space usage. This also helps to determine whether there are any active transactions preventing the log reuse.

Here’s how to run this command:

Now, follow the below methods to resolve the error 9002 in SQL Server.

Method 1: Create Transaction Backup File

The error usually occurs when the transaction log file is full and can’t be truncated. Many SQL users have confirmed that they’re able to resolve the SQL Server error 9002 by recreating the transaction backup file. When you back up the transaction log file, the SQL Server automatically truncates the committed transactions. You can also try to recreate the transaction log file and see if resolves the issue.

Method 2: Check and Change Log File Growth Settings

If the restricted growth of transaction log file is the reason for the error, then increase its size to allow more space for transactions. To do this, use the sys.dm_db_log_space_usage command to monitor log space use. It will show the current size and maximum size of the log file. You can enlarge the log file by using the MODIFY FILE clause or ADD LOG FILE clause in the ALTER DATABASE statement (see the below example).

Method 2: Shrink the Transaction Log File

You can also try shrinking the log file. It releases the unused space by removing the empty space from the file. It can help resolve the SQL Server error 9002. To shrink the log file, run the below T-SQL script:

Method 3: Enable Autogrowth for the Log File

The transaction log file gets out-of-space, if the Autogrowth option is not enabled. You can check and enable this option by using the below steps:

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
• Expand Databases, right-click on the database, and then click Properties.

• In the Database Properties window, click Files and then click three dots (…) under Autogrowth/Maxsize.

• In the Change Autogrowth window, check whether Enable Autogrowth is checked. If not, select it. Click OK.

Method 4: Check and Free Disk Space

The transaction log file is generally saved on your system’s hard drive. Lack of storage on your disk can cause the SQL Server error 9002. So, you can check and free up some space on the disk by deleting or moving other files. To do this, follow the below steps:

• First, right-click on the drive where your transaction log file is saved and then click Properties.

• In the Properties window, you can check the free space. If the disk space is low, you can free-up the space by moving some files to another drive.

You can also move the transaction log file to another drive with enough space.

What If SQL Database gets Corrupted?

The SQL server error 9002 can cause improper application shutdown or incomplete transactions, which can lead to corruption in the database. You can check corruption in SQL database by using the DBCC CHECKDB command (see the below example).

If the database file is corrupted, you can try restoring the database from backup using the below command:

If your backup file is not readable or is obsolete, you can repair corrupt SQL database using the DBCC CHECKDB command. This command can help you repair the database, but it doesn't guarantee complete data recovery and can cause data loss.

To prevent data loss, you can use Stellar Repair for MS SQL. It is an advanced SQL repair tool that can repair NDF/MDF files with complete precision. It can repair corrupt SQL databases in Always-on-Availability group or failover clusters. It can recover all the objects, including deleted records, from the damaged/corrupted SQL database file. You can even preview the objects in the repaired database before saving them. You can save the repaired database file to a new or live database and formats like CSV, HTML, and XLS. It can help in resolving all errors related to corruption in SQL database.

Conclusion

The SQL Server error 9002 can prevent you from restoring the backup file. The error occurs when the log file is full and can’t be truncated. In this article, we’ve shared the solutions that can help you resolve the SQL Server error 9002. If your SQL database file is corrupted, then you can use Stellar Repair for MS SQL to repair and restore the corrupt database quickly and precisely. The tool can repair MDF/NDF file of any size and from any version of SQL Server. It can help recover all the data from SQL database including tables, stored procedures, indexes etc. with complete integrity.