Friday, February 5, 2010

SQL Server Error 9004 Attach Database Failed

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.

Microsoft suggest 2 options for this 9004 error, let me explain the steps in details. Click here for BOL. 

Option 1: 
First you have to create an empty database with the same name and physical file structure, then shut down the server, swap the both .log and .mdf files you want to attach in place of the empty DB files, and start the server. After above step the database should come up in suspect mode. You can then run this script ALTER DATABASE <database name> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS . The above command clear the some data in log to make the database consistent, but may have to delete some data in order to make the database consistent.  This is the best option which is most likely to get the maximum data back. 

Option 2: 
You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back.  If the database was cleanly shut down, you MIGHT be able to succeed.  There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back.  You should run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent (Please click here to know how to run repair allow data loss). If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required. 
how to resolve 9004 error. my log file was crashed. how to rebuild my log file. how to restore corrupted log file. my log file corrupted getting error 9004. how to use repai_allow_data_loss. how to resolve 9004 error. most common error in sql server 2000. sql server 2012 error. sql server 2008 R2 error. sql database corrupted. sql db corrupted. suspect database. attach database. detach database. database restore error. database consisteny error. database status error. database inconsistency. in ms sql server. ms sql 2008 r2. how to use sql database. sql database in use. sql server 2008 r2. detach db. ms sql servers. checkdb for database. sql server checkdb. checkdb sql server. ms sql server tutorial. sql restore database in use. restore sql database in use. corrupt sql database. sql 2008 checkdb. data base administrator. data base administrator. microsoft database administrator. sql questions and answers. database administrator questions. sql server interview questions and answers download.


Post a Comment