Emergency sql как исправить

Summary: Emergency mode is used to access a database marked in RECOVERY PENDING or SUSPECT state. At times, when attempting to bring the database back to normal mode, the database is stuck in EMERGENCY mode. This blog explains the steps following which you can recover a SQL database from EMERGENCY Mode to NORMAL Mode. It also discusses an alternative solution, using a SQL recovery tool to quickly restore the inaccessible database to its original (normal) state, without data loss.

Free Download for Windows

Contents

  • How to Recover SQL Database from Emergency Mode to Normal Mode?
  • Alternative to Recover Database from EMERGENCY to NORMAL Mode
  • Conclusion

The EMERGENCY mode is a read-only status used to read data from databases in the RECOVERY PENDING/SUSPECT mode. Essentially, when a database status is changed to SUSPECT, it becomes inaccessible. Setting a database in EMERGENCY mode helps access the database and run a repair on it using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command.

Since the database is under repair, it might take you a few minutes to bring the database back online. But, if the database seems stuck in EMERGENCY mode, try to bring it ONLINE by executing this command:

ALTER DATABASE database_name SET ONLINE

Note: if your database goes into SUSPECT state due to corruption, setting it to ONLINE state will just return a database in SUSPECT status. In that case, you cannot access the database information unless you bring it back to its normal state.

If the database is still in an EMERGENCY state, you will need to manually repair your database.

How to Recover SQL Database from Emergency Mode to Normal Mode?

Follow these steps to manually repair a SQL database and restore it to normal mode.

Step 1 – Check the Database State

Before proceeding, check if the database is still in EMERGENCY mode by running the following command:

SELECT name, state_desc FROM sys.databases WHERE name = Test_Database

This command will return the state of the database ‘Test_Database’.

database in emergency state

As you can see the ‘Test_Database’ is in EMERGENCY state. So, let’s repair the database manually.

Step 2 – Repair the SQL Database Manually

Before starting the repair procedure, it’s important to switch the database to SINGLE_USER mode to prevent other users from using the database. Once the database is in SINGLE_USER mode, repair it by running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command.  

ALTER DATABASE Test_Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECKDB (Test_Database, REPAIR_ALLOW_DATA_LOSS)

The CHECKDB command runs successfully without any consistency errors.

dbcc checkdb

Now put the database in MULTI_USER mode.

ALTER DATABASE Test_Database SET MULTI_USER

Now refresh the database and it will be back online.

Alternative to Recover Database from EMERGENCY to NORMAL Mode

If your database is stuck in EMERGENCY mode, try to look for the last backup to restore the database and recover its information. If the backup is corrupt or unavailable, use a SQL recovery tool like Stellar Repair for MS SQL to restore your database to its original state. The software helps restore a SQL database by repairing .mdf/.ndf data files. 

free-download

The software allows saving the restored data to a new or an existing SQL database. Also, it supports saving the data in file formats like CSV, HTML, or XLS.

Conclusion

The EMERGENCY mode is used when a SQL Server database is stuck in RECOVERY PENDING or SUSPECT mode and is no longer accessible. Changing the database status to EMERGENCY helps read the data from the inaccessible database. A situation may arise when you fail to bring the database back ONLINE from EMERGENCY state. This may happen when the database is still getting repaired. So, wait for some time to check the database state. If it doesn’t return to its normal mode, set the database state to ONLINE. If this doesn’t work, you will need to manually repair the database to bring the database back to normal mode. Another option is to restore the database from a valid backup. If nothing works, use Stellar Repair for MS SQL to make the database accessible with minimal downtime.

About The Author

Charanjeet Kaur

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.

“The database of SQL Server that I am using has suddenly showing database in suspect mode. The issue is appearing for the first time so I have no clue about it. One of my friends told me that I can access it by putting the database in emergency mode. Now I am not able to recover it. Please tell me how to recover database from emergency mode in SQL Server?”

Are you an SQL Server user and your database got marked as suspected? Do you want to know how to recover SQL database from emergency mode in SQL Server? If your answer is yes to these questions, then continue reading this post.

Many SQL users often face situations when SQL database become suspect. This issue gives rise to a problem as users become unable to access that database. Users have to wait till the database returns to normal mode. And the situation becomes worse if there is no backup of that data.

SQL users can Sthe circumstances by putting the database in emergency mode. It allows users to access their database even when it is marked as suspected. Using the mode, users get Read Only permission to access the suspected database hence can recover their data from emergency mode.

In this write-up, we will discuss method on how to recover database from an emergency mode in SQL Server, but first, let us understand What are the possible reasons for going Database in suspect mode.

Note: In case if the users wants to quickly recover Microsoft SQL Server database objects then the user can take the help automated solution.

Download Now Purchase Now

What Causes SQL Server Database in Suspect State

Before learning the solution to this issue, have a look at the facts that give rise to the condition.

  • Database Corruption: If your SQL database is marked as suspected, it means that something suspicious is going in it. The major cause of this issue is often the corruption in the SQL database itself.
  • Log File Corruption: If any transaction log file goes missing or becomes damaged, the database comes in Suspected mode.
    Sudden Server Shutdown: The unexpected shutdown of SQL Server can also put SQL Database is in Suspect Mode.
  • Virus or Malware Infection: Any kind of virus or malware attack can also be responsible for SQL Server database suspected mode.
  • Unstable Power Supply: If the power supply to the database is not stable, various issues may occur that can lead to making your database inaccessible.
  • Hardware Issues: Hardware problem can also become a reason for this SQL database problem.

What to Do if SQL Database is in Suspect Mode

If the users have a backup of their data, they can access it. Otherwise, the only way to access the data is to turn on the emergency mode for the database. If you use SQL Server 2005 and its newer versions, this method is compatible with your database.

How to Recover Database from Emergency Mode in SQL Server?

In order to recover SQL database from emergency mode, users have to go through these steps.

1. Confirm the Suspected Status of SQL Database

The first thing that needs to be done in this case is to check the suspected state of the database. Users can check this by using the below command to access data from the database. If the database has turned into suspected one, the command will result into an error message.

SELECT *FROM database_name..table_name

2. Enable Emergency Mode for SQL Server

Once you are sure about the suspect mode of SQL database, you have to put the database in the emergency mode. For that, use this command:

ALTER DATABASE database_name SET EMERGENCY

3. Repair SQL Database

After the emergency mode is on, users should proceed to repair SQL Server Database . This repairing will help to remove all the inconsistencies responsible for the suspected mode. In order to do that, the database should be turned into single user mode. Users must remember that they can face some data loss during this repairing process.

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB(database_name,REPAIR_ALLOW_DATA_LOSS)

GO

4. Switch the Database Back to Multi-User

When the repairing is over, permit multiuser access to the database with this command:

ALTER DATABASE database_name SET MULTI_USER WITH ROLLBACK IMMEDIATE

5. Online the Database

Finally, run this command to leave the emergency mode and put the database online.

ALTER DATABASE database_name SET ONLINE

Final Words

In this post, we have discussed how to recover database from emergency mode in SQL Server. We have also learned what causes SQL Database in Suspect Mode and how emergency mode in SQL Server allows us to access the suspected database. Users can take the help of the manual solution of this issue given here to recover SQL database from emergency mode. But the problem is that this method is not suitable for the large sized database. For the sure-shot solution to recover SQL database emergency mode, choose SysTools MDF database repair tool. This application is an ideal solution to recover database from emergency mode in SQL Server.

Frequently Asked Questions

Can I recover SQL database from emergency mode to normal mode using backup?

Yes, you can restore the database from healthy backup to get rid of SQL database corruption problem.

What is emergency mode in SQL Server?

Emergency mode is a status introduced for SQL Server to process damaged databases, especially those in suspect mode. The Emergency mode is used when the SQL Server database is in suspect mode and we switch to emergency mode to read the data.

Can I recover deleted database objects in SQL Server?

Using SQL database repair tool, you can recover deleted tables, views, triggers, stored procedures and other database components.


Siddharth Rawat

Read time 4 minutes

Many SQL users are facing an adverse situation where their SQL database becomes Suspect. Due to this issue, the users are unable to access their databases. This might create a nuisance to the users if there is no backup of that data. The way around this issue is by setting the database in emergency mode. In emergency mode, the user can lose their access to the database as it can be locked in a read-only mode, but still, the data recovery is possible. In this post we will look at the steps on how to repair corrupt SQL database and recover from emergency mode to normal mode.

Some of the possible causes behind the SQL database going in suspect mode are

  • Corruption of Database: If your SQL database is marked suspect, then the SQL database might have gone corrupt.
  • Corruption of Log File: Every SQL database comes equipped with a transaction log that records all transactions and database modifications. Any transaction log file getting corrupt can be the root cause of the SQL database going corrupt.
  • Improper System Shutdown: This is the most common reason for almost any corruption issue. Hence it is suggested to avoid improper system shutdowns.
  • Malicious Attacks: Any sort of malware attack is also responsible for the SQL server database going into suspect mode.
  • Hardware Issues: Any hardware problems can also be associated with this issue.

Steps to Recover SQL database from Emergency mode to Normal mode

Step 1: Check the Suspected Status of SQL Database

It is always better to check whether the SQL database is in the suspected state or not. This can be checked using T-SQL. If the SQL database has gone into the suspected mode, then this should result in an error message.

Step 2: Enable Emergency Mode for SQL Server

Now, if you are sure of the suspected status of the database, then the next step would be to put the database in the emergency mode. To do so, use the following command:

Step 3: Repair SQL Database

Once you have turned on the emergency mode, then the user to now proceed to repair the SQL server database. This will help to fix any issues with the SQL database. During the process, the database will be put into single-user mode. It is eminent that during this procedure, there might be some data loss.

Step 4: Switch Back to the Multi-User

Please enter the following command to switch back to allow multi-user access to the database:

Step 5: Set the Database Online
The final step is to set the database online, which can be accomplished with the following command:

Note: One can also use manual methods to recover SQL database without backup.

Kernel for SQL Database Recovery

It is possible to recover your information if your SQL database is corrupt. Just in case your backup has gone corrupt, you can still restore the database using the Kernel for SQL Recovery tool. With this tool, all database objects, including tables, triggers, rules, functions, deleted records, and more are recoverable. In addition, one can recover deleted records too. Large-sized MDF/NDF files can also be recovered with this tool. After recovery, it automatically creates a backup of restored databases in script form. The preview feature allows the user to view the recovered database content before saving it to the destination. It directly uploads the recovered MDF/NDF files to the LIVE SQL server.

SQL database recovery

Conclusion

We have looked at the steps to alter the SQL database from Emergency mode to Normal mode. In such situations, one can also restore the databases from healthy backup. In case your backups have also gone corrupt, then one should look for trustworthy and tested SQL database recovery tools. In the case of SQL database corruption, we highly recommend Kernel for SQL Database recovery.

  • Remove From My Forums
  • Question

  • Hello Experts,

        I am learning SQL Server 2008, I placed a test database in emergency mode using the below command:

       alter database test set emergency.

        Now I want to remove the database from  emergency mode to normal mode. So I executed the below command:

       Alter database test set online.

      But the command is keep running for more than 15 mins. I also checked the state of the database using the below query and found the database is still in emergency state:

    select name,state_desc  from sys.databases where name = ‘test’ 

     Please let me know how to bring database from emergency to normal.

    With regards,

    Gopinath.


    With regards, Gopinath.

Answers

  • It might take couple of minutes to check your db before bring it online it might doing data repairs — if it fails then you have to do it manually 

    here are steps

    ALTER DATABASE Test SET SINGLE_USER;
    GO
    DBCC CHECKDB (Test, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; 

    Go
    Alter DATABASE test set MULTI_USER;

    —-

    Hope this helps you 


    http://uk.linkedin.com/in/ramjaddu

    • Proposed as answer by

      Friday, April 27, 2012 7:14 PM

    • Marked as answer by
      KJian_
      Thursday, May 3, 2012 6:12 AM

EMERGENCY-mode repair: the very, very last resort




  • Paul Randal




  • October 2, 2007

(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:

  1. Hack the system tables to get the database into EMERGENCY mode.
  2. Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.
  3. 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).
  4. 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
  5. Take the database out of EMERGENCY mode
  6. 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.

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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAEmergencyDemo_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAEmergencyDemo_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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAEmergencyDemo_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 APPLECROSSpaul 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

  1. 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

  2. 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

  3. 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

    1. 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;

  4. 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

    1. 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!

      1. 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.

  5. Super! You’re info has brought up my db up and running again!

  6. 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

  7. 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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataICS.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

  8. 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?

  9. 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…

  10. Really brilliant you saved the day of many DBA and Administrators with this article,
    Thanks a lot

  11. 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.

    1. 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.

  12. 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

  13. 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!

  14. 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.

  15. 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:ERPDBKAE_fromKAE.mdf». Operating system error 5: «5(error not found)».
    File activation failure. The physical file name «F:ERPDBKAE_Retail_4_log.ldf» may be incorrect.
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file «F:ERPDBKAE_fromKAE.mdf». Operating system error 5: «5(error not found)».
    File activation failure. The physical file name «F:ERPDBKAE_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.
    ———————————————————————————————————-

  16. 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?

  17. 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 :-).

  18. Thank you

  19. You saved me! Thank you very much for this information. I thought we were done for.

  20. 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

    1. Do:
      ALTER DATABASE XYZ SET OFFLINE
      ALTER DATABASE XYZ SET ONLINE

      If they succeed, nothing should prevent the backup succeeding.

      1. Thank you thank you thank you!!! :D yeah!!!

  21. 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?

    1. It’s impossible for me to say – totally depends on what transactions were in flight when the crash occurred.

  22. 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

    1. 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).

  23. 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.

    1. Glad to have helped! Next time, shrink the log file using DBCC SHRINKFILE instead of deleting it.

  24. Thanks i got everything working again.

  25. 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

    1. 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.

  26. Dear Paul,

    I’ve got the following error when trying to recover:
    File activation failure. The physical file name “F:_backup_17_07_13DATAAlfaMedia_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:sqluserdbAlfaMedia_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_13DATAAlfaMedia_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:sqluserdbAlfaMedia_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.

    1. No – the data files are corrupt too. This database is lost completely.

  27. This article is amazing, thank you!

  28. 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

    1. 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.

  29. 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!

  30. I have found this article really helpful in resolving suspect database.

  31. I just wanna say thank you!

    It works fine, feeling like a hero right now.

    Best regards

  32. 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,

    1. Try setting the database OFFLINE then ONLINE again.

  33. I was really struggling trying to figure this out until I found this blog post. Solved my problem in a matter of minutes. Thanks!

  34. 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.

    1. No – everything’s the same, but your SharePoint will likely be unusable after forcibly removing data from it.

  35. 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_ERROMSGS should be WITH ALL_ERRORMSGS

    1. Oops – cut and paste error – thanks

  36. 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?

    1. 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.

  37. Its really nice article, it worked for me what i was supposed to fix. Thanks

  38. 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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAXYZ_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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAXYZ_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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAXYZ_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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAXYZ_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

    1. 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.

      1. 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

        1. You need to go page by page. See the example in Module 6 of my advanced corruption Pluralsight course.

          1. 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.

  39. 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…

    1. Thanks – good to know!

  40. Saved my bacon!!!! Thanks so much.

  41. 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:DBDataSQL2008R2MyDBFile.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?

    1. 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.

  42. 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

    1. Glad to hear it!

  43. Paul,

    Genius, you saved my day at Siempresoft Perú!

    1. Excellent!

  44. 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:foodpossqlsqldborderdb.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.

    1. 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.

  45. 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.

    1. 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.

      1. Guess that’s what i’ll have to do.

        Thanks For the Quick response Paul.

  46. 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 !

  47. Thanks Paul, very detailed article indeed!
    Keep up the good work!

  48. This guide literally saved me 4 days of work

    1. Excellent!

  49. This article is a gold mine. Very well written. Thank you!

  50. 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!

    1. You can do them in parallel, but I wouldn’t recommend it because they’ll be competing for resources and run significantly slower.

  51. 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. 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.

      1. 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 :).

  52. You shouldn’t ever be allowed to retire.

    1. Ha! Thanks :-)

  53. 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

    1. 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!)

  54. Thank you very much. This sorted my issue. Stacked for one week

  55. 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 FilesMicrosoft SQL ServerMSSQL15.RED7TECH_ALCHEMYMSSQLDATARhod_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.

    1. Restore from backups or try a third-party recovery tool.

Leave a Reply

Other articles

The Mentoring Class of 2023

(Meeps, one of our seven cats, helping with the selection process…) Since my first round of community mentoring back in 2015, when I mentored 54

Explore

The Curious Case of… community

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Понравилась статья? Поделить с друзьями:

Не пропустите также:

  • Как на казахском будет слово найти
  • Как на армянском найдешь
  • Как найти электронные слои атома
  • Как найти сопротивление резистора онлайн
  • Как найти девушку для прогулки

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии