You are currently offline, waiting for your internet to reconnect

How to troubleshoot Error 9004 in SQL Server

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Symptoms

An operation in SQL Server that needs to read or process the transaction log can fail with an error like the following if the transaction log is damaged:

Error: 9004, Severity: 21, State: 1.
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.

The State number can vary for this error and indicates what type of damage has occurred with the log. See the More Information section about State numbers.

In most cases, this error is just 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 this error is often seen 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 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 or DBCC CHECKDB. To find the cause for the damage of the log you should follow the similar techniques for database corruption including an analysis of possible hardware, filesystem, and/or I/O problems. See the Cause section of the following article for more information: How to troubleshoot database consistency errors reported by DBCC CHECKDB.

Resolution

You should restore from a known good backup to recover from this problem. It is possible that if the transaction log portion of a database backup or the transaction log backup itself has damaged transaction log contents, you can encounter an Error 9004 on RESTORE. In this situation, the transaction log in the backup is damaged.

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 including the possible loss of transactional consistency in your database. To read about how to rebuild the transaction log, please see the section titled Resolving Database Errors in Emergency Mode in the SQL Server Books Online under the DBCC CHECKDB command

More Information

The SQL Server Engine 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 what type of failure was encountered when processing the transaction log:

  • State 1 = The log file header of the Virtual Log File (VLF) was damaged.  If a damaged log file header is encountered 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 in the log file. This is not the same as the file header or the first 8KB of the log file. If the file header of the the log file is damaged you will encounter Msg 5172 as with a database file header page corruption.
  • State 2 and 3 = A log block was invalid when performing recovery during RESTORE
  • 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
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 2015753 - Last Review: 04/01/2010 18:22:00 - Revision: 1.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Express with Advanced Services, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup

  • KB2015753
Feedback
var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" 區 - 繁體中文
El Salvador - Español
Panamá - Español
Uruguay - Español
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
0&did=1&t=">amp;did=1&t="> var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" ')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> y>