(New for 2020: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)
(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.)
This is a follow-on from two posts:
- Corruption: Last resorts that people try first… where I discussed the two worst things you can do (in my opinion) to a database – rebuilding the transaction log and running REPAIR_ALLOW_DATA_LOSS.
- Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database where I introduced EMERGENCY mode and walked through an example script showing its use.
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.
Well, I expected that not to work. Let’s run EMERGENCY-mode repair:
DBCC CHECKDB (N'EmergencyDemo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO
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.
Hmm – looks like the failed ALTER DATABASE statement did change the state – but what to?
SELECT
[state_desc]
FROM
sys.databases
WHERE
[name] = N'EmergencyDemo';
GO
state_desc ----------------- RECOVERY_PENDING
That makes sense because the log file does not exist. The recovery system knows that recovery has to run on the database but it is unable to start without the log file.
Back to EMERGENCY mode and runing repair:
ALTER DATABASE [EmergencyDemo] SET EMERGENCY; GO DBCC CHECKDB (N'EmergencyDemo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO
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.
ALTER DATABASE [EmergencyDemo] SET SINGLE_USER; GO DBCC CHECKDB (N'EmergencyDemo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 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. 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.
Checking the database state:
SELECT
[state_desc]
FROM
sys.databases
WHERE
[name] = N'EmergencyDemo';
GO
state_desc ----------- ONLINE
We find that it’s been brought back online again because everything worked. Let’s see what happened to our table:
USE [EmergencyDemo]; GO SELECT * FROM [salaries]; GO
FirstName LastName Salary ---------- ----------- ------- John Williamson 10000 Stephen Brown 0 Jack Bauer 10000 (3 row(s) affected)
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.
90 thoughts on “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.
JMB
Paul,
Brilliant, you saved my day!
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!
Best Wishes in New Year!
Vladimir
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
when trying to run the dbcc checkdb, I kept getting a message saying the database was in recovery mode. to fix it, I had to do this:
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE — <<<<<<—— this was the key for me
— I then did this (which I probably shouldn't have done):
ALTER DATABASE MyDatabaseName SET ONLINE
ALTER DATABASE MyDatabaseName SET MULTI_USER WITH NO_WAIT
— then I did all of this again and it worked:
ALTER DATABASE MyDatabaseName set emergency
ALTER DATABASE MyDatabaseName set single_user with no_wait
dbcc checkdb(N'MyDatabaseName',repair_allow_data_loss) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Hi Ravi,
Is there any activity in the database (i.e. is recovery still running)? Do you get this message when you try to set the database into emergency mode? How did you get into this state? etc etc
Thanks
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.
The version is SQL Server 2008 R2 (RTM) Standard Edition (64-bit).
Hopefully Mr. Paul can advise me ASAP. Thank you so much in advance!
Try setting the database offline and then online. It’s also possible you’ve hit a bug, as it’s pretty ridiculous to still be running the RTM build of 2008 R2.
Super! You’re info has brought up my db up and running again!
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.
regards and great thanks
raimund
Paul,
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.
Thanks,
Josh
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.
ALTER DATABASE db_name SET EMERGENCY
Command(s) completed successfully.
ALTER DATABASE db_name SET SINGLE_USER
Command(s) completed successfully.
SELECT state_desc FROM sys.databases WHERE name=’db_name’
RECOVERY_PENDING
No matter what we do, the state is always ‘RECOVERY_PENDING’
DBCC CHECKDB (db_name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
Msg 922, Level 14, State 1, Line 1
Database ‘db_name’ is being recovered. Waiting until recovery is finished.
Server does not show any activity – how can I tell if anything is actually happening?
What can we try next?
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…
Really brilliant you saved the day of many DBA and Administrators with this article,
Thanks a lot
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.
Your original db is now in emergency mode.
Worked like a charm. following the steps above.
Error: 3313, Severity: 17, State: 5
After 100% restored if was either rolling forward or rolling back the log records during the backup phase.
During redoing of a logged operation in database ‘DATABASE_NAME’, an error occurred at log record ID (249778:11829914:133).
And after the last restart for SQL server the DB came online OK without the DBCC checkdb command. GO figure.
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.
regards
Your info was what I was missing to fix my SUSPECT database. It seemed to be quite difficult to find this info on the internets. Thanks very much Paul!
Paul,
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.
Dear Paul,
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.
———————————————————————————————————-
I am trying to recover a 2000 DB on a 2008 box, all the steps worked until I got to the DBCC. Now I get the error
Msg 946, Level 14, State 1, Line 3
Cannot open database ‘CompTecDataSQL1’ version 539. Upgrade the database to the latest version.
Is there a fix for this?
You saved my day Paul. Thanks for posting this wonderful article. As some of the mentioned, you surely deserve a pay raise or a one month paid vacation at Bahamas :-).
Hi. I hope you don’t mind I published a link to your site. great article, thanks!
http://thelonelydba.wordpress.com/2013/01/30/sql-database-in-emergency-mode/
Thank you
You saved me! Thank you very much for this information. I thought we were done for.
Thank you very much for this article.
I used this procedure but now I cannot backup the database.
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)
I cannot backup using FILEGROUP or FILE.
The database is online.
I even created a FULLTEXT…but still.
Hoping to get a save here :).
Any suggestions?
Thanks
Do:
ALTER DATABASE XYZ SET OFFLINE
ALTER DATABASE XYZ SET ONLINE
If they succeed, nothing should prevent the backup succeeding.
Thank you thank you thank you!!! :D yeah!!!
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?
It’s impossible for me to say – totally depends on what transactions were in flight when the crash occurred.
When I run DBCC CHECKDB(DB_NAME,REPAIR_ALLOW_DATA_LOSS) I get the following errors:
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:152) in database 7 failed. It belongs to allocation unit 562949953880064 not to 983040.
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:152) in database 7 failed. It belongs to allocation unit 562949953880064 not to 983040.
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:152) in database 7 failed. It belongs to allocation unit 562949953880064 not to 983040.
DBCC results for ‘MAS500_app_E’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘MAS500_app_E’.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
Is there any hope? We have bad backups and only this mdf file. There’s actually an mdf, ndf and ldf.
Any advice?
Thank you,
MJG
Nope – the repair failed. Your only course of action now is to extract as much data as you can from the damaged database into a new database (and fix your backup + backup testing strategy).
Damn you are lifesaver.
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.
Glad to have helped! Next time, shrink the log file using DBCC SHRINKFILE instead of deleting it.
Thanks i got everything working again.
Hello Paul,
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?
Any advice for me?
Best Regards,
Torsten
There’s another connection to the database. Set the database to multi_user again, and then single_user using WITH ROLLBACK IMMEDIATE to kill all the connections. You may have to restart SSMS too.
Dear Paul,
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?
Thank you.
No – the data files are corrupt too. This database is lost completely.
This article is amazing, thank you!
Hi Paul,
I am getting the following error while trying to run the repair, any ideas where to go to from here
Msg 946, Level 14, State 1, Line 1
Cannot open database ‘XYZ’ version 539. Upgrade the database to the latest version
Thanks,
Barry
I would copy the files and attach them to a 2000 server and try there. It’s a 2000 database that’s failed upgrade it seems.
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!
I have found this article really helpful in resolving suspect database.
I just wanna say thank you!
It works fine, feeling like a hero right now.
Best regards
Hi Paul,
I fallowed the above steps and I get the following error when I run:
DBCC CHECKDB ([MYDB]) WITH NO_INFOMSGS
“Database ‘MYDB’ is being recovered. Waiting until recovery is finished.”
When I check the State of the Database it says “RECOVERY_PENDING”
However, it looks like nothing is happening.
IS there anything that can be done to recover this DB?
Best regards,
Try setting the database OFFLINE then ONLINE again.
I was really struggling trying to figure this out until I found this blog post. Solved my problem in a matter of minutes. Thanks!
I am in same senerio but using sharepoint 3.0 and microsoft internal database ssee. Is there in change in commamds or some other methods without emergency mode.
No – everything’s the same, but your SharePoint will likely be unusable after forcibly removing data from it.
There’s a typo in the code below the line “Well, I expected that not to work. Let’s run EMERGENCY-mode repair:”
WITH ALL_ERROMSGSshould beWITH ALL_ERRORMSGSOops – cut and paste error – thanks
Hi,
I need to run the recovery commands inside .exe file. Cammands are as follows
USE MASTER
ALTER DATABASE [elroc] SET OFFLINE
ALTER DATABASE [elroc] SET ONLINE
EXEC sp_resetstatus [elroc]
ALTER DATABASE [elroc] SET EMERGENCY
DBCC checkdb([elroc])
ALTER DATABASE [elroc] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([elroc], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [elroc] SET MULTI_USER
ALTER DATABASE [elroc] SET ONLINE
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.
IM sure that there is no another connection to the database.
any idea please?
There must be some error coming back from SQL Server for your case – you need to debug that and handle it in your code. There’s nothing else you can do with your T-SQL.
Its really nice article, it worked for me what i was supposed to fix. Thanks
Hello Mr. Paul
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
Thanks in advance,
BTW using SQL SERVER 2008 R2
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
BR
You need to go page by page. See the example in Module 6 of my advanced corruption Pluralsight course.
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.
Thanks in advance and sorry again.
Fabulous article Paul, I had a demo come up as “In Recovery” on me, went through the steps (WHICH WERE PERFECT!) right up until the point I received this error…
Msg 41836, Level 16, State 1, Line 28
Rebuilding log is not supported for databases containing files belonging to MEMORY_OPTIMIZED_DATA filegroup.
Msg 41316, Level 23, State 3, Line 3414
Restore operation failed for database ‘memoryoltp’ with internal error code ‘0x0000000a’.
Yet another reason to be very careful…I think this demo is done…
Long time fan, first time poster…
Thanks – good to know!
Saved my bacon!!!! Thanks so much.
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.”
According to microsoft support https://support.microsoft.com/en-us/kb/2699013 i should apply a hotfix, to avoid the last one, but i have the most recent hotfix for this version
Do you know about another workaround?
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
Glad to hear it!
Paul,
Genius, you saved my day at Siempresoft Perú!
Excellent!
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.
Looks like the file is totally trashed if it can’t even read the file header page. You might try replacing the file header page by changing the example at https://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-a-broken-boot-page/ to use offset zero in the current file and an older restored copy. Otherwise it’s game over for that database I’m afraid if you don’t have an older backup.
Hello Paul,
after running DBCC CHECKDB (Database, REPAIR_ALLOW_DATA_LOSS)
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.
Guess that’s what i’ll have to do.
Thanks For the Quick response Paul.
Thank you so much for the awsome article
I used it today to recover a damagen databse in emergency mode , and ofc the customer had no backup ! ( He has now :) )
It workeds brilliant and only 4 records loss ( the damages ones)
Thank you so much !
Thanks Paul, very detailed article indeed!
Keep up the good work!
This guide literally saved me 4 days of work
Excellent!
This article is a gold mine. Very well written. Thank you!
Hello Paul,
In a Dev environment, I had 5 databases that were in RECOVERY PENDING. I have checked the Production backups: they are fine on other downstream servers.
I have fixed the first two, using the methods outlined in this post.
The last 3 are larger databases.
My question(s) is/are: should I run the CheckDB on the remaining databases one at a time? Or can I run on two different databases concurrently to speed things up?
I think the answer is: ‘No’.
But I wanted to check. I wondered if running CheckDB (on multiple databases) at the same time will cause blocking, and slow the remaining work I have ahead.
Restoring the databases (as they’re development), is not an option at the moment.
Cheers!
You can do them in parallel, but I wouldn’t recommend it because they’ll be competing for resources and run significantly slower.
I’ve been practicing corrupting data, detecting it and repairing in in a 2016 AO lab. Your articles on this are simply fantastic. Thank you for taking the time to share this information with us. I have two questions – The first is seeking confirmation of something I think I know. After I set the database offline and then single-user and finally in to emergency mode I could not set it to on-line. I kept getting errors about the missing log file. I could get in to the DB and look at the Salary table. For some reason, it dawned on me to run sp_who2 and see what was happening. I discovered that there was a session open in the test database. After i killed it I was able to set the DB on-line and execute the DBCC command and bring it back to multi-user. Should I have expected an error about being in single-user mode? The version is listed below:
Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) – 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
The second question concerns transactions that were not committed. In this exercise we were updating one table when we took down the database, and we knew what to look at. In reality, there could have been hundreds or more transactions taking place (as if you don’t know that!). Any advice on where/what to look at in order to discover what else was lost? Someone might ear hero status for getting the database back up, but if the data is a mess, then only half the job is done. Is that even possible?
1) Not sure exactly what you did – single-user wouldn’t have been possible if there was another connection to the database.
2) That’s the big question. Without log backups to restore from, who knows what state the data is in. That’s why people need to write their own application consistency checker – which really no-one ever does. To avoid the need for this, have log backups always, plus multiple synchronous copies of the database in different physical locations.
Actually, I have been caught several times when putting a DB into Single User mode by SSMS. The SSMS Object Explorer table is doing scans of different DBs at different times, and will not allow others into the DB. Normally this happens at the most annoying times. It could also be some of the different DB/Network monitoring tools. Just my 2 cents :).
You shouldn’t ever be allowed to retire.
Ha! Thanks :-)
Hey Paul, had a friend who ran repir_allow_dataloss but the process seemed stuck at 99% after 6 hours.
CPU was still ticking over at 20%.
Is there any way to see deeper in what its doing, or to tell if it will evenetually just fail,
or how long it will take please?
Great post by the way…really helpful.
Cheers
Steve
There isn’t I’m afraid. When you’re running repair, the percent complete isn’t accurate as it doesn’t know exactly how long each repair will take. I would let it finish. (and thanks!)
Thank you very much. This sorted my issue. Stacked for one week
Hello.
I’m hoping to help with using an MDF that gives me “Could not continue scan with NOLOCK due to data movement” when I try to follow recovery steps.
I have an MDF file recovered from a damaged SSD (LDF has gone).
As part of the recovery I have created a database, stopped SQL server, swapped the MDF and started SQL Server.
CMD: ALTER DATABASE [Rhod] SET ONLINE;
Msg 5173, Level 16, State 1, Line 7
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘C:\Program Files\Microsoft SQL Server\MSSQL15.RED7TECH_ALCHEMY\MSSQL\DATA\Rhod_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5181, Level 16, State 5, Line 7
Could not restart database “Rhod”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 7
ALTER DATABASE statement failed.
CMD: ALTER DATABASE [Rhod] SET EMERGENCY;
Msg 601, Level 12, State 3, Line 21
Could not continue scan with NOLOCK due to data movement.
CMD: ALTER DATABASE [Rhod] SET SINGLE_USER;
Msg 601, Level 12, State 3, Line 34
Could not continue scan with NOLOCK due to data movement.
What can I do next to recover database or extract data from damaged MDF?
Thanks
Alex.
Restore from backups or try a third-party recovery tool.