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:\data\adventureworks2019.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.
Was this article helpful?
