locked
Database in Emergency Mode RRS feed

  • Question

  • Hi All.. i have had one database was in suspect mode and i have changed the status to Emergency Mode and  i have run the DBCC checkdb and command didn't gave any error.. after this i have tried to change database to Single_user mode, but command running successfully, but database status is still showing as Emergency mode only.. i have tried for multi_user mode too..

    Also, i have tried to run the command to bring online, but command running for long time with Suspended status..

    Could anyone help me on this??

    Tuesday, March 31, 2015 6:53 AM

Answers

All replies

  • Could you run below command and confirm it still stuck in emergency mode:

    SELECT state_desc FROM sys.databases WHERE name='db_name'


    Tuesday, March 31, 2015 7:10 AM
  • Hi, please check your event viewer and SQL Server LOG. Should be a logical consistency-based I/O error although it is strange that DBCC has reported no errors

    Best regards


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Tuesday, March 31, 2015 7:37 AM
  • Hi Balajik,

    "Also, i have tried to run the command to bring online, but command running for long time with Suspended status.."

    In addition to other post, please check the following things.

    1. Run sp_who2 to check that if there is blocked process. If blocked process exists, run “KILL <spid>“ to kill it.
    2. Make sure that there are no connections to the database from other applications. You can run the below scripts to find connections.

    USE master
    
    SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('DatabaseName')

    3. You might need to stop and start SQL Server Service using SQL Server Management Studio, then check if you can set the database online.

    Besides, to recover database which is in emergency mode, you can review the following similar thread.
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4186ba24-5188-4ea9-bb83-f39d44f1897d/database-in-emergency-mode?forum=sqldatabasemirroring

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support




    Wednesday, April 1, 2015 7:33 AM