During startup recovery of a database, if there are severe consistency problems detected, the database will enter a SUSPECT state. You can observe the following errors in the error log that shows the failure.
2005-07-22 09:45:15.51 spid19s Starting up database 'dbcc_emergency_repair1'.
2005-07-22 09:45:17.70 spid19s Error: 5243, Severity: 22, State: 1.
2005-07-22 09:45:17.70 spid19s An inconsistency was detected during an internal operation. Please contact technical support. Reference number 6.
2005-07-22 09:45:17.73 spid19s Error: 3313, Severity: 21, State: 2.
2005-07-22 09:45:17.73 spid19s During redoing of a logged operation in database 'dbcc_emergency_repair1', an error occurred at log record ID (29:398:16). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
2005-07-22 09:45:17.79 spid19s Error: 3414, Severity: 21, State: 1.
2005-07-22 09:45:17.79 spid19s An error occurred during recovery, preventing the database 'dbcc_emergency_repair1' (database ID 11) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2005-07-22 09:45:18.49 spid5s Recovery is complete. This is an informational message only. No user action is required.
When you query sys.databases, the state for this database will show as SUSPECT. Now you can put this database into EMERGENCY mode using the command:
ALTER DATABASE dbcc_emergency_repair1 SET EMERGENCY
As soon as you run this command, you will notice the following information logged into the error log and the database will immediately transition into a EMERGENCY state.
2005-07-22 09:47:06.43 spid51 Setting database option EMERGENCY to ON for database dbcc_emergency_repair1.
2005-07-22 09:47:06.48 spid51 Starting up database 'dbcc_emergency_repair1'.
2005-07-22 09:47:06.53 spid51 The database 'dbcc_emergency_repair1' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.
As you will normally do, running a regular DBCC CHECKDB in the EMERGENCY mode will provide you with the report of all the consistency and allocation issues present in the database.
DBCC CHECKDB ( dbcc_emergency_repair1 )
CHECKDB found 11 allocation errors and 2 consistency errors in table 'test1' (object ID 2073058421).
CHECKDB found 21 allocation errors and 2 consistency errors in database 'dbcc_emergency_repair1'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbcc_emergency_repair1 ).
2005-07-22 09:48:04.93 spid51 DBCC CHECKDB (dbcc_emergency_repair1) executed by REDMOND\sureshka found 23 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
The next step is to attempt the repair option with the DBCC CHECKDB command. But first you will need to put the database in single user mode.
ALTER DATABASE dbcc_emergency_repair1 SET SINGLE_USER
SQL Server error log should show the following entry indicating the database is in single_user mode.
2005-07-22 09:49:26.84 spid51 Setting database option SINGLE_USER to ON for database dbcc_emergency_repair1.
Now we are ready to run the CHECKDB command with the repair option.
DBCC CHECKDB (dbcc_emergency_repair1, REPAIR_ALLOW_DATA_LOSS)
You will notice the following information in the regular result set produced by the DBCC CHECKDB command.
Warning: The log for database 'dbcc_emergency_repair1' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
DBCC results for 'dbcc_emergency_repair1'.
CHECKDB found 11 allocation errors and 1 consistency errors in table 'test1' (object ID 2073058421).
CHECKDB fixed 11 allocation errors and 0 consistency errors in table 'test1' (object ID 2073058421).
CHECKDB found 21 allocation errors and 1 consistency errors in database 'dbcc_emergency_repair1'.
CHECKDB fixed 21 allocation errors and 0 consistency errors in database 'dbcc_emergency_repair1'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbcc_emergency_repair1 repair_allow_data_loss).
At the same time, SQL Server error log will also show the following entries.
2005-07-22 09:50:31.53 spid51 Starting up database 'dbcc_emergency_repair1'.
2005-07-22 09:50:31.90 spid51 Error: 5243, Severity: 16, State: 1.
2005-07-22 09:50:31.90 spid51 An inconsistency was detected during an internal operation. Please contact technical support. Reference number 6.
2005-07-22 09:50:31.90 spid51 Error: 3313, Severity: 21, State: 2.
2005-07-22 09:50:31.90 spid51 During redoing of a logged operation in database 'dbcc_emergency_repair1', an error occurred at log record ID (29:398:16). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
2005-07-22 09:50:31.93 spid51 Starting up database 'dbcc_emergency_repair1'.
2005-07-22 09:50:32.35 spid51 Starting up database 'dbcc_emergency_repair1'.
2005-07-22 09:50:32.53 spid51 Warning: The log for database 'dbcc_emergency_repair1' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
2005-07-22 09:50:32.53 spid51 Warning: The log for database 'dbcc_emergency_repair1' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
2005-07-22 09:50:33.65 spid51 EMERGENCY MODE DBCC CHECKDB (dbcc_emergency_repair1, repair_allow_data_loss) executed by REDMOND\sureshka found 22 errors and repaired 21 errors. Elapsed time: 0 hours 0 minutes 2 seconds.
If you notice carefully here, the events in the error log clearly show the sequence in which the repair option for the DBCC CHECKDB command operated. First you can observe that the database was started and it encountered the same startup problems. After that the log for the database was rebuilt and then the check was run with the repair.
Any time, you run a DBCC CHECKDB with repair when the database is in EMERGENCY mode, you will notice the error log entries clearly indicate this and differentiate it from a regular DBCC CHECKDB run. The error log entry will clearly show as “EMERGENCY MODE DBCC CHECKDB”.
Once the EMERGENCY mode repair is complete, you will need to change the accessibility option for the database from SINGLE_USER to MULTI_USER. Also you may want to run a DBCC CHECKDB to ensure the database is completely clean. After this you will need to perform a Full Database backup to start a new backup chain.
Thanks
Huzeifa Bhai
Now users can easily fix SQL errors & repair database from suspect mode by using SQL recovery tool. Get more information: http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html
ReplyDelete