Table of Contents
- Introduction
- Root Cause of the Error
- What to do
- Restore from Backup
- Rebuild the Transaction Log
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.
| description | title | ms.custom | ms.date | ms.service | ms.reviewer | ms.subservice | ms.topic | helpviewer_keywords | ms.assetid | author | ms.author |
|---|---|---|---|---|---|---|---|---|---|---|---|
|
MSSQLSERVER_9004 |
MSSQLSERVER_9004 | Microsoft Docs |
04/04/2017 |
sql |
supportability |
reference |
9004 (Database Engine error) |
b528bb49-340c-4a81-9c8d-cefce6562f16 |
MashaMSFT |
mathoma |
MSSQLSERVER_9004
[!INCLUDE SQL Server]
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 9004 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | LOG_CORRUPT |
| Message Text | An error occurred while processing the log for database ‘%.*ls’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. |
Explanation
An error was encountered while processing the log during rollback, recovery, or replication. This could indicate an error detected by the operating system or an internal consistency error detected by [!INCLUDEssNoVersion].
The [!INCLUDEssDEnoversion] performs logical checks on the consistency of the transaction log contents as it reads and processes it. Not all aspects of the log header, log blocks, and log records are checked. The State number provides more information on the type of failure:
- State 1 The log file header of the Virtual Log File (VLF) was damaged. If a damaged log file header is found as part of starting up the database on service startup, you may only see Error 9004 in the ERRORLOG. The Log File Header is the first portion of each VLF inside of a transaction log. The log file header is a not the same as the single file header, or the first 8 KB, of the log file. If the file header of the log file is damaged, you may get Msg 5172, similar to a database-file header-page corruption.
- State 2 and 3 A log block was invalid when performing recovery during a RESTORE operation.
- State 4 through 12 These are all various checks on log blocks when processing log records. These including parity, sector, and other logical checks on the consistency of the transaction log
In most cases, this error is only seen in the ERRORLOG or Windows Application Event Log with EventID = 9004 because the operation processing the log is not based on a direct user command (such as recovery running when the SQL Server Engine starts). In these situations, error 9004 is often seen together with Error 3414. However, some queries such as ALTER DATABASE could require a processing of the log and therefore will see these errors. Since the error is at Severity=21, the user session is disconnected.
Cause
Error 9004 is a general error indicating the contents of the transaction log are damaged. The reason for the log to become inconsistent are similar to any database corruption problem detected by the SQL Server Engine. To find the cause for the log damage, you should follow similar techniques used for database corruption including an analysis of possible hardware, filesystem, and I/O problems. Note that DBCC CHECKDB does not check the transaction log as part of its operations and cannot detect log corruption errors. Error 9004 is raised by SQL Server Engine itself.
User Action
One of the following actions will correct this error:
-
Restore from a backup: Restore from a known good backup to recover from this problem. It is possible that, if the log portion of a database or log backup contains damaged contents, you encounter an Error 9004 on RESTORE. In this situation, the transaction log in the backup is damaged.
-
Rebuild the log: If you cannot restore from a backup, you may be able to bring the database online by rebuilding the transaction log. You should carefully understand the ramifications of rebuilding the transaction log. This includes possible loss of transactional consistency in your database. For more information on how to rebuild the transaction log, please see Resolving Errors in Database Emergency Mode.
-
Examine Logs for system issues: Also, check the System Event log and Errorlogs to identify issues within the system that may have caused the problem.
My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log.
Error: 9004, Severity: 23, State: 6. An error occurred while processing the log for database 'MyDB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Is there any way to fix this without taking the database offline?
If not, what would be the fastest way? Transactional data loss would be accepted.
asked Jul 28, 2014 at 11:20
5
Not knowing much else about your problem, if your database is online and you’re not using anything that may be actively using part of the log — such as Mirroring, AGs, Replication, etc, you could attempt removing that part of the log from the sweep by changing to the simple recovery mode (if in bulk or full) and issuing a checkpoint. Then go back to your normal recovery model and either take a full or differential to restart a valid LSN chain.
This may or may not work for you depending on what the actual underlying cause is, but should work for most cases caused by a state of 6.
Please note: I am not advocating that you do anything to compromise your database. If the database is online and functioning, this won’t cause any data loss nor would it be destructive other than to your LSN chain which can be bridged.
answered Jul 28, 2014 at 12:07
Sean GallardySean Gallardy
26.6k3 gold badges37 silver badges73 bronze badges
1
I don’t know why, but deleting and recreating the replication on the database caused the error not to reappear.
answered Aug 4, 2014 at 7:55
3