People get themselves into situations where they have no backups (or damaged backups) and the data or log files are damaged such that the only way to access the database is with EMERGENCY mode. In these situations, prior to SQL Server 2005, there was no documented or supported way to fix a database while in EMERGENCY mode – the only guidance could be found on the Internet or from calling Product Support and paying for help. The sequence of events was:

Hack the system tables to get the database into EMERGENCY mode.

Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.

Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files – both those that may have caused the issue, and those caused by rebuilding the transaction log (e.g. because an active transaction altering the database structure was lost).

Figure out what data was lost or is transactionally inconsistent (e.g. because a transaction altering multiple tables was lost) as far as your business logic is concerned

Take the database out of EMERGENCY mode

And then all the other stuff like root-cause analysis and getting a better backup strategy

How Does EMERGENCY-Mode Repair Work?

I decided to add a new feature to SQL Server 2005 called EMERGENCY-mode repair that will do steps 2 and 3 as an atomic operation. The reasons for this were:

Much of the advice of how to do this on the Internet missed steps out (particularly missing step 3!)

The DBCC REBUILD_LOG command was unsupported and undocumented and we didn’t like advising customers to use it

Adding a documented, last-resort method of recovering from this situation would reduce calls to Product Support – saving time and money for customers and Microsoft.

So, when in EMERGENCY mode, you can use DBCC CHECKDB to bring the database back online again. The only repair option allowed in EMERGENCY mode is REPAIR_ALLOW_DATA_LOSS and it does a lot more than usual:

Forces recovery to run on the transaction log (if it exists). You can think of this as ‘recovery with CONTINUE_AFTER_ERROR‘ – see this post for more details on the real CONTINUE_AFTER_ERROR options for BACKUP and RESTORE. The idea behind this is that the database is already inconsistent because either the transaction log is corrupt or something in the database is corrupt in such a way that recovery cannot complete. So, given that the database is inconsistent and we’re about to rebuild the transaction log, it makes sense to salvage as much transactional information as possible from the log before we throw it away and build a new one.

Rebuild the transaction log – but only if the transaction log is corrupt.

Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option.

Set the database state to ONLINE.

It’s a one-way operation and can’t be rolled back. I always advise taking a copy of the database files before doing this in case something goes wrong or there are unrepairable errors. And if it does? Probably time to update your resume for not having a water-tight backup and disaster-recovery strategies in place. I have seen it fail several times in real customer situations. It’s not pretty when it does.

EMERGENCY-Mode Repair Example

Let’s walk-through an example of using it. I’m assuming there’s a database called EmergencyDemo that’s in the same state as at the end of the second post I link to at the beginning of this post: the database has no log file, is in EMERGENCY mode and the salaries table is corrupt.

First off I’ll try bringing the database online, just to see what happens:

ALTER DATABASE [EmergencyDemo] SET ONLINE;
GO

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EmergencyDemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 945, Level 14, State 2, Line 1
Database 'EmergencyDemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Failed to restart the current database. The current database is switched to master.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

The first message makes sense – the database knows it needs to be recovered because it wasn’t cleanly shut down, but the log file simply isn’t there. The second message is from the new feature in 2005 that will automatically create a log file if one is missing on startup or attach – as long as the database was cleanly shut down. The 945 and 5069 errors are self-explanatory but notice that the database gets switched to master underneath us. This has bitten me several times in the past.

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode – however, the sys.databases field state_desc will still just say EMERGENCY.

Btw, if the ALTER DATABASE statement to set the database into EMERGENCY mode fails, try setting the database OFFLINE and then ONLINE again first. Then set EMERGENCY mode and continue with the code below.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EmergencyDemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Warning: The log for database 'EmergencyDemo' 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.

This time it worked. First of all we get the same error as if we tried to bring the database online – that’s from the code that’s trying to run ‘recovery with CONTINUE_AFTER_ERROR‘ on the transaction log. Next we get a nice long warning that the transaction log has been rebuilt and the consequences of doing that (basically that you need to start a new log backup chain by taking a full or differential backup). If there had been any corruptions we’d have seen the usual output from DBCC CHECKDB about what errors it found and fixed. There’s also a bunch of stuff in the error log:

2013-01-20 15:45:45.550 spid51 Starting up database 'EmergencyDemo'.
2013-01-20 15:45:45.560 spid51 Error: 5105, Severity: 16, State: 1.
2013-01-20 15:45:45.560 spid51 A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\EmergencyDemo_log.LDF' may be incorrect. Diagnose and correct additional errors, and retry the operation.
2013-01-20 15:45:45.560 spid51 Starting up database 'EmergencyDemo'.
2013-01-20 15:45:45.610 spid51 Starting up database 'EmergencyDemo'.
2013-01-20 15:45:45.630 spid51 Warning: The log for database 'EmergencyDemo' 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.
2013-01-20 15:45:45.730 spid51 EMERGENCY MODE DBCC CHECKDB (EmergencyDemo, repair_allow_data_loss) WITH all_errormsgs, no_infomsgs executed by APPLECROSS\paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

Note that the usual error log entry from running DBCC CHECKDB is preceded by ‘EMERGENCY MODE‘ this time.

And of course its still corrupt – because even though the transaction log was rebuilt and repaired, the original transaction that changed the salary to 0 never got a chance to rollback because I deleted the transaction log (in the previous post).

Now remember, you should only use this as a last resort, but if you do get yourself into trouble, you know there’s a command that should be able to help you.

74 Responses to EMERGENCY-mode repair: the very, very last resort

Paul:
Your article called "CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort" is awesome. It just saved my butt. You are the only one on the web the steps you through the process to recover from a suspect database, step by step. Thanks. You deserve a huge pay raise, where ever you work at now. Best of future to you in your future endeavors. Godspeed my friend.

I accidentally deleted log file after setting database offline, then found out that it’s not the same as "detach". Bingo, I expected at least a couple of days just to reimport 80,000,000 records from the so called Death Master File. Thanks to your article, everything was fixed in 30 minutes!

When I do this that is when database is in recovery pending state and when i try to follow the above steps..it says Database ‘dbWiki’ is being recovered. Waiting until recovery is finished.
And the recovery goes on for a long time. Not sure if the DB is even being recovered. What is the ay out

Hi Mr. Paul,
I am getting the same error when I am running the dbcc checkdb (N’ABC’,REPAIR_ALLOW_DATA_LOSS).
Error is:
Msg 922, Level 14, State 1, Line 1
Database ‘ABC’ is being recovered. Waiting until recovery is finished.

There is no recovery running. Also, I have no problem setting the db to EMERGENCY mode.

well, i’m really glad, that you went through all those steps and shows therefore how to repair hopeless cases, running under sql-server 2005
unfortunately, most examples on the internet are written for sql 2000 and don’t get a glimpse of this new way unter 2005.

I get the same message as ravi above. Is it because you can’t go into emergency with a bad boot page?

Can a db be set to emergency mode if the database boot page is torn as in the following error:

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: torn page
(expected signature: 0xaaaaaaaa; actual signature: 0x56aaaaaa). It occurred
during a read of page (1:9) in database ID 7 at offset 0x00000000012000 in
file ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ICS.mdf’.
Additional messages in the SQL Server error log or system event log may
provide more detail. This is a severe error condition that threatens
database integrity and must be corrected immediately. Complete a full
database consistency check (DBCC CHECKDB). This error can be caused by many
factors; for more information, see SQL Server Books Online.

Tried your steps on a database that had 3 files (Data, Index and Log) where we only have Data left (Index, Log and Backups were all on RAID5 external array that blew up.
We have re-created the database, stopped SQL 2k5, replaced Data file and started SQL.

Hmm – given that you’ve got a missing data file (the index one) this may be a lost cause. You could try setting that file offline, and setting all the indexes that were on it offline too. As long as they’re just nonclustered indexes, you *might* be able to get that to work – but this is a lot more complicated. Topic for another blog post…

if you get "Database ‘db_name’ is being recovered. Waiting until recovery is finished. "

Follow the following steps:
1 stop sql server
2 rename your db files or move them to another location
3 start sql server
4 remove the database from the list
5 create a new database with the same name and the same datafiles
6 set this database in emergency mode
7 stop sql server
8 copy your original db files back
9 start sql server.

Thanks a lot Paul, you’re the man. I was in the last devconnections in Orlando and I was in all your sessions. A couple of days later I had a very big corruption problem and your session and this were really, really, really helpfully. Once again thanks a lot.

I already used this command ALTER DATABASE db_name SET EMERGENCY after that database has come in emergency mode but when i used this command ALTER DATABASE db_name SET SINGLE_USER,
it has run sucessfully but database status not changed Emergency to Single User mode.

I have a database in restore state with corrupted log.
I did put it into emergency mode and performed a plain DBCC checkdb(‘KAE_Retail_4’) and had no consistency errors.

However when I try the DBCC CHECKDB (KAE_Retail_4, REPAIR_ALLOW_DATA_LOSS) I get the following messages.

I would really appreceiate ANY HELP or Suggestion.

—————————————————————————————————–
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "F:\ERPDB\KAE_fromKAE.mdf". Operating system error 5: "5(error not found)".
File activation failure. The physical file name "F:\ERPDB\KAE_Retail_4_log.ldf" may be incorrect.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "F:\ERPDB\KAE_fromKAE.mdf". Operating system error 5: "5(error not found)".
File activation failure. The physical file name "F:\ERPDB\KAE_Retail_4_log.ldf" may be incorrect.
Msg 5028, Level 16, State 5, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
———————————————————————————————————-

System.Data.SqlClient.SqlError: The backup of the file or filegroup “XYZ” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

Thank you for the post. I was able to follow things and it seems like it is recovering the database. The question I have is how long should it take roughly for the recovery to be completed. My database size is around 1.2GB with around 5 million records and around 50 or so tables. It is now 40 minutes and it is still saying “Exceuting Query”. Does this seem to be normal?

I had stupidly deleted the LDF file to try and free up some space on the server.
None of these commands worked on the server but i managed to install an instance of SQLExpress on my local PC then create the database –> stop sql server –> replace MDF with the one i was trying to recover –> start Sql server–>ran your commands and lo and behold it worked. Made a backup of the database and restored it to its location.

On the plus side the ldf is now back to a reasonable size.
I did have a DB backup but it was a day old.

I have to bring back a db that was not clean shut down and the log file was deleted. I followed your instructions and have the db in single_user and emergency mode. If I try to run DBCC CHECKDB(DB_NAME,REPAIR_ALLOW_DATA_LOSS) I get this error message: (free translated from german message, don’t know the original english text)
The Database is already opened and can only have one user.

I run all the commands within MS SQlManagement Studio. Is that the reason for this error?
How will I be able to run the DBCC CHECKDB command?

I’ve got the following error when trying to recover:
File activation failure. The physical file name “F:\_backup_17_07_13\DATA\AlfaMedia_MSCRM_log.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘D:\sql\userdb\AlfaMedia_MSCRM_log.LDF’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
File activation failure. The physical file name “F:\_backup_17_07_13\DATA\AlfaMedia_MSCRM_log.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘D:\sql\userdb\AlfaMedia_MSCRM_log.LDF’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.

Why does it say so and is it still possible to recover DB without backup?

Thanks for the great article Paul; you just saved me a ton of time with my client today who had a hard drive fail on them this week. I was in one of your team’s classes last year in Chicago too which was also great. Keep of the great work! :) I still have (and use) the cup!

normally exe file runs perfectly for all steps. But when the database enters emergancy or suspect status. EXE File fails to run. The only way is to use the same commands inside management studio. One repair inside man.stud. Exe file can run perfectly again. Seems smthng blocks the exe file.

I’m facing the same issue you described in your article, a database server crashed and we extracted the .mdf and .ldf files from the file system but it appears that a specific database “XYZ” was running some operations when the crash happened as when I was trying to attach i got the following error:
The log scan number (218:387:1) passed to log scan in database ‘XYZ’ is not valid

so I hack attached it by creating a database with the same name and replaced the .mdf file and delete the new .ldf after stopping SQL SERVER Service to reproduce your scenario, and started it again.

i followed your instructions as follows:
ALTER DATABASE [XYZ] SET EMERGENCY;
GO

ALTER DATABASE [XYZ] SET SINGLE_USER;
GO

DBCC CHECKDB (N’XYZ’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
but when i ran the DBCC CHECKDB Command i faced the following message

File activation failure. The physical file name “E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\XYZ_log.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\XYZ_log.ldf’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
File activation failure. The physical file name “E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\XYZ_log.ldf” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\XYZ_log.ldf’.
Msg 5024, Level 16, State 2, Line 1
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 1
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.

But i’m noticing something weird, Why it’s trying to create the log file in this path C:\etc… my sql server installation and all the paths within it are on the E:\etc… is this because the old sql server installation was on C:, is there any help

The data file is corrupt so the pathname for the log file is damaged – so it can’t create a new log. You’ll need to user emergency mode and just extract information into a new database, as it can’t create a new log file for you.

Thank you for your fast reply, i’ve extracted around 80% of the data from this database, but there are a specific table “which is the most importent one” when i try to select records from it to insert it in another database I recieve the following Error

Msg 601, Level 12, State 2, Line 1
Could not continue scan with NOLOCK due to data movement.

is there a way to exract at least the non corrupted pages/records from this table

Thank you Mr. Paul for replying and I’m very sorry for bothering you, I tried to access the pluralsight.com course but it requires a billing info which i don’t have, and i wasn’t able at all to find documents or tutorials talking about this topic “reading from a table page by page”, if you can give me at least a starting point it would be very helpful as i’m desperate here.

Hi Paul,
First, thanks for this post, it has been useful a couple times, but now…

I am working with one customer who has a SQL Server instance in Microsoft SQL Server 2008 R2 (SP3-OD) (KB3144114) – 10.50.6542.0 (X64) Ent Edition x64 (yes I know, they should upgrade )
When I set the database to Emergency Mode i get the message:
“Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement. ”
even that message error the databse is setting to Emergency mode, but i can’t read any user table or system table, if i perform the CHECKDB with REPAIR_ALLOW_DATA_LOSS, it starts, but after a while (3%)
I get the messages in sqlerrorlog like:
“Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:145093511; actual 0:0). It occurred during a read of page (1:145093511) in database ID 44 at offset 0x000114be70e000 in file ‘G:\DBData\SQL2008R2\MyDBFile.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”
after many of those errors i get messages like:
“Process 0:0:0 (0x8fc) Worker 0x0000000005F221A0 appears to be non-yielding on Scheduler 4. Thread creation time: 13128420781808. Approx Thread CPU Used: kernel 47970 ms, user 0 ms. Process Utilization 1%. System Idle 96%. Interval: 18951189 ms.”

No workaround for this, as the corruption looks like it’s in system tables. You could try doing individual DBCC CHECKTABLEs to figure out which system table is broken, and then you’ll likely have to manually hack around the corruption. Most cases like this are lost causes, and the company just has to accept the data loss and fix their backup and DR strategies.

Thanks Paul – worked on SQL Server 2012 R2 when I very erroneously detached a database pending Recovery after a power loss during operation. The most important data, the stored procedures, were intact even though the log and data were basically hosed. Luckily the data can be rebuilt easily… not so much the procs though!
Cheers,
MB

Hi Paul,i am using sqlexpress 2005. I’ve done the steps for the db above since my database is in emergency. Here is the error encountered:

Msg 922, Level 14, State 1, Line 4
Database ‘foodposodb’ is being recovered. Waiting until recovery is finished.
Msg 823, Level 24, State 6, Line 3
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file ‘D:\foodpossql\sqldb\orderdb.MDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

i get Failed to restart the current database. The Current database is switched to master.
Msg 5028. Level 16, State 4, Line 1
The system could not activate enough of the database to rebuld the log.
DBCC results for ‘SNTRSRV’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘SNTRSRV’.

Msg 7909, Level 20, State 1, Line 44
The emergency-mode repair failed.You must restore from backup.

Yup – your database is too badly corrupt for emergency-mode repair to work. You’re going to have to restore from your backups, or try to manually extract information from the database in emergency mode.