Ошибка 9004 sql

Introduction

Transaction logs in SQL database are the vital component consisting of all the changes a user makes in database. Moreover, user can undo the changes that he made in a file of the database using the information contained in the
transaction logs; this operation is also a transaction. It can be understood by the following:

You have made some changes in the data file of a database, say DB1, it means that a transaction is taking place. The log begins keeping record by the time you start making changes till you perform commit of that transaction. Note that a database consists
of minimum one data file and physical transaction log.

Now imagine that there is damage to transaction log, it will directly impact the database. If an operation is performed in SQL that requires processing or reading the transaction log, an error may occur similar to the following:

Error: 9004
An error occurred while processing the log for database.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Root Cause of the Error

The error occurs in case of damage to contents of transaction log. The severity of the error is same as that of a database corruption. Therefore, for in-depth analysis of cause, the similar techniques should be applied that are required for database corruption.

What to do

Try out the following to work around this problem:

Restore from Backup

It is recommended to bring the backup into use. It might be the case that the backup of transaction log or its portion has created corruption in the contents of transaction log. In this case, Error 9004 may occur while restoring. It indicates that there
is damage to transaction log placed in the backup.

Rebuild the Transaction Log

If it is not possible to restore from the backup, you can rebuild the transaction log. However, you need to be aware of all positives and negatives of doing it so that you can avoid possible transactional consistency loss in the database. Furthermore, it
includes
DBCC CHECKDB command to execute.

However, you might not be able to carry out the task successfully or even if you do, it might not give the expected results. If so, you can use commercial SQL recovery application. Such applications are the programs built by software experts. More of it,
such software will perform recovery, which has been failed by DBCC CHECKDB command, it will also recover the database from suspect mode. You can also recover the database in case of severe damages.

In SQL Server, the database contains the data file with information of the tables, views, stored procedure, etc. and the transaction log files. The transaction log files store all the transactions and modifications. For example, if you update, insert, or delete data in the tables or views, these changes are stored in the transaction logs.

The SQL Server error 9004 is related to a problem with the transaction logs. The error indicates that the transaction log is corrupt. It can be a problem with the log file header or the Virtual Log File (VLF), which can be damaged. The corruption may occur due to unexpected server or service shutdown, hardware failure, virus or malware attacks, etc.

Let’s discuss how to resolve the SQL Server error 9004.

Solutions to Fix the SQL Server Error 9004

Here are some solutions you can try to resolve this error and recover the database.

Restore the Database using Backup

If you have a backup of your database, the best option is restore your database from the last backup.

For this, open SQL Server Management Studio (SSMS) and go to the Object Explorer pane. Right-click the database to backup and select Tasks > Back Up.

• In the Backup Database window, select the Full Backup type, the destination path, and then press OK.

• If you need to restore the data, in the Object Explorer, right-click the database and select the Restore Database option.

• In the Restore Database window, select the database to restore and select the backup sets to restore, and press OK.

Note: It is strongly recommended to press the Verify Backup Media button before restoring.

Use DBCC CHECKDB Command to Repair the Database

You use the DBCC CHECKDB command to repair the corrupt database.

Here’s the syntax:

USE master;
ALTER DATABASE Adventurewords2019 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB (Adventurewords2019, REPAIR_FAST) WITH NO_INFOMSGS;
GO
ALTER DATABASE Adventurewords2019 SET MULTI_USER;
GO

You need to first set the database in single-user mode. This mode will log out other users of the database. Then, run the CHECKDB command to repair the database.

After repairing the database, you can set the database to multi-user mode to allow other users to access the restored database again.

If the REPAIR_FAST command fails, in the DBCC CHECKDB command, you can try the following options:

• REPAIR_REBUILD: It will try to rebuild the database.
• REPAIR_ALLOW_DATA_LOSS: It repairs the database but you may lose some data.

Use CREATE DATABASE Command with FOR ATTACH_REBUILD_LOG

There is an option in SQL Server to create the database and rebuild the transaction log file. The syntax to rebuild the database is:

USE [master]
GO
CREATE DATABASE [AdventureWorks2019] ON
( FILENAME = N’c:dataadventureworks2019.mdf’ )
FOR ATTACH_REBUILD_LOG
GO

First, you need to go to the master database. Then, create the database and rebuild the corrupt log.

Use a Third-Party SQL Repair Software

You can use a third-party SQL repair software, such as Stellar Repair for MS SQL. This software can repair corrupt databases without any data loss.

The software is simple to use. You need to first find the data file.

Note: Make sure that your database is offline before using the software.

To find your data file, open Stellar Repair for MS SQL and press the Find button.

• To find your data file, you can open the Stellar Repair for MS SQL and press the Find button.

• Select the database file and press the Repair button to start the repair process.

Once the repair process is finished, you can export the repaired data to a SQL Server database or various other formats, such as Excel, CSV, HTML, etc.

Conclusion

The SQL Server 9004 error usually occurs due to corruption in transaction logs or database. In this article, we learned how to fix this error. You can restore the database from the backup or use the DBCC CHECKDB commands to repair and recover the database. However, for quick and easy recovery of the database, use a specialized SQL repair tool, such as Stellar Repair for MS SQL.