Using the Emergency State for a Corrupt SQL Server Database

By:   |   Comments (15)   |   Related: > Disaster Recovery


I noticed a new feature that was first introduced in SQL Server 2005, which is the Emergency state for a database. It looks that it may facilitate dealing with a suspect database. How can I use the Emergency state for a database effectively while working with a suspect database?


During a disaster recovery process, the Emergency state provides flexibility to perform several operations on a corrupt/suspect database. When a database is put in the Emergency state, it makes three major changes to the database configuration:

  • Makes the database READ ONLY
  • Restricts the access to members of sysadmin fixed server role only
  • Logging is disabled (as database is READ ONLY)

As you can see it helps by putting the database in a read only and restricted access mode, but the real power of the Emergency state is the fact that you can use it for a Suspect database.

By putting a Suspect database in the Emergency state you have direct access to the data and many of the database configuration settings. Although a suspect state is not a prerequisite for putting a database in an Emergency state, this is probably the most useful time that you would want to use the Emergency state. When a database is in the Emergency state you can access your data, so you can export it to another database and you can even run a DBCC CHECKDB on the database to fix corruption.

In the following sections we will walk through how to use this feature.

Create a database in SUSPECT state

To show an example, we will create a suspect database. So first create a database with some data in it as shown below.

--Script 1: Create and populate database


USE ForEmergency

CREATE TABLE students (rollNo TINYINT, name VARCHAR(15))

SELECT 5,'Asad'

There is more than one approach to turn a database to a suspect state, but if you follow the below steps you can create a suspect database.

  • Stop SQL Server service
  • Open the .ldf file for database ForEmergency with a text editor, make a change to some data in the file and save and close the file
  • Start SQL Server Service

Note: if you are using Windows 7 or Windows Server 2008 you may need to copy the file to another location and then paste back to the original location after modification.

The database should now have a Suspect status and it can be verified by running the following statement.

--Script 2: Check database status


In this state we are not able to access the data in the database.

--Script 3: Try to access data in suspect database

SELECT * FROM ForEmergency..students

If we try to access data in the suspect database, we will get the following error message.

Msg 945, Level 14, State 2, Line 2
Database 'ForEmergency' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'students'.

Put database in EMERGENCY state

As we are not able to access the database in Suspect state, let's convert it to Emergency state.

--Script 4: Set database to Emergency mode


Emergency status can be confirmed through Script 2 above or in SSMS as shown below.

confirm emergency status in ssms

Options in EMERGENCY state

After a suspect database is put in Emergency state, it is now accessible with some limitations. The database may be accessed as READ ONLY, so you can export the data out of the database by using any method like BCP, DTS or SELECT INTO statement. In addition, various database settings may be manipulated including:

  • Recovery model
  • Auto shrink, Auto update Statistics
  • Cursors Settings
  • ANSI NULL settings

These settings may be accessed in the Options frame for the database in SSMS as shown below.

the settings may be accessed in the options frame for the database in ssms


DBCC statements can also be used when the database is in an emergency state. We can run DBCC CHECKDB to check for corruption. In addition, we can use the REPAIR_ALLOW_DATA_LOSS option. When using the REPAIR_ALLOW_DATA_LOSS option, damaged data or indexes may be removed to make the database physically consistent. As the option indicates, by using this option anomalies of the database will be removed which may cause some data loss. In addition, if there are problems with transaction log corruption, this command will rebuild the log file.

Note: to avoid any data loss, try all possible recovery options especially restoration from a database backup before using this option.

In the below code we are using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option to fix the corruption for the database. Also note that in order to fix the corruption we need to put the database in single user mode.

--Script 5: Run DBCC CHECKDB command



In our case the transaction log is corrupt and it will be rebuilt. If the command is successful the database will be put in an Online state and could be switched back to multi-user mode by using this command.


Now the database is online and accessible in a physically consistent state. The rebuilt transactional log may result in a loss of transactional consistency in the target database, so it would be a good idea to run DBCC CHECKCONSTRAINTS to find any anomalies in data relations.

Put database in ONLINE/OFFLINE state

To leave the Emergency state and turn to either an ONLINE or OFFLINE state you can use these commands.

--Script 6: Set database to Normal mode

ALTER DATABASE ForEmergency SET ONLINE -- put the database online if corruption is fixed

ALTER DATABASE ForEmergency SET OFFLINE -- put the database offline

Next Steps

Although the Emergency state is a great option, it is not always a sufficient approach for large databases. Pulling out a large amount of data and making it ready for use is problaby not a feasible option the amount of time you are given to resolve the problem. So the most important thing to remember is that there is no alternate for a good and sound backup strategy. In the event of any type of disaster proper backups would save a lot of time and effort in the recovery process.

Some other notes:

  • A database marked as RESTORING cannot be set to EMERGENCY. Also, such databases can not be set to OFFLINE or ONLINE either.
  • Click here to read about different database states in SQL Server 2005/2008

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, November 30, 2017 - 9:58:31 AM - RAMPRASATH Back To Top (73476)

PLEASE BE INFROM THAT  in above senario he corrupted log file and there is noting to do with checkdb.

Checkdb won't check log file it only checks the data file bcz data file only have pages and not the log file, If you try to corrupt a data file and try above steps it won't work.


Let me know if I'm wrong or how to recover the data file by above steps.

Monday, July 10, 2017 - 2:56:41 AM - Bhavesh patel Back To Top (59172)

Thanks Atif,
In sql server 2012, I have faced live issue, Server A connected mirror with Server B (synchronous mirror without witness), When db goes in suspect mode in principal, I have break mirror on mirror side and up the product, but issue is that when i go for recovery in principal side it raised error like " it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group"
I have removed mirror both side and not configured HA, despite this, i also removed end point and linked server both side because of I have unable to performed this steps. when i ran step "ALTER DATABASE yourDBname SET EMERGENCY" it throw error as per mentioned above, please suggest...


Wednesday, January 20, 2016 - 6:59:00 AM - Lokesh Kumar V Back To Top (40450)

 Thanks a TON my system DataBase got up.......................... from the above procedure.


Tuesday, November 19, 2013 - 3:15:50 AM - Atif Back To Top (27534)

You cannot set msdb to emergecy state. Try to restore a backup. Steps are provided in following tip.



Tuesday, November 5, 2013 - 12:24:41 PM - sultana Back To Top (27397)

my msdb   shows suspect mode. it happened due to improper shutdown of my computer.

how can I fix it. I am  in learning stage and the sql server is 2012 evaluation  



Monday, October 29, 2012 - 1:25:58 PM - Shahid Back To Top (20132)

Atif Bhai,

Great Article. Helped a lot today to get a DB back online.

Wednesday, January 25, 2012 - 11:16:34 AM - Joshua Guttman Back To Top (15776)

Excellent article. This keeps me from having to hack the sysdatabases table. :-) Love the beard. I have to get mine under control. I'm somewhere between Santa Clause and the unibomber. :-)

Friday, March 25, 2011 - 1:13:35 AM - Atif Shehzad Back To Top (13318)

@Ahmad. I have tried several scenarios on suspect database and was able to turn database to offline or emergency state. Just want to repeat a point mentioned at the end of tip "A database marked as RESTORING cannot be set to EMERGENCY.  Also, such databases can not be set to OFFLINE or ONLINE either.". So i think there is no problem to change state of database from suspect to emergency/offline. If you get any problem in your case then please mention scenario and error statement here.


Thursday, March 24, 2011 - 2:32:02 AM - Ahmad Back To Top (13305)

Is there any case when Suspect DB can even not be changed to offline or emergency state?



Thursday, March 24, 2011 - 2:08:34 AM - Atif Shehzad Back To Top (13303)

Suspect DB can be changed to offline or emergency state. Is there any other status that you want to set?

Thursday, March 24, 2011 - 1:40:03 AM - Ahmad Back To Top (13302)

Yes, Atif Thanks for your response!


"access the data and settings in the suspect database we can use emergency state." But actually DBMS didnt allowed to update status of suspect DB! :)


Thursday, March 24, 2011 - 1:25:17 AM - Atif Shehzad Back To Top (13301)

@Ahmed. A database in suspect state has many restictions. You can not access the data in suspect database, DBCC statements can not be used to repair the database. To provide a way to access the data and settings in the suspect database we can use emergency state. It provides a lot of flexiblity to repair and save data from suspect database. After a suspect database is put in emergency state, you can access data in it along with trouble shooting and verification statements like DBCC CHECKDB.

Thursday, March 24, 2011 - 12:43:43 AM - Ahmad Back To Top (13300)

But is this possible that after the DB status is set as Suspect, due to any reason, and then even SQL server dont allow to execute commmands to change the DB State? Sorry i had seen this in actual!!!

Let explain a bit!





Wednesday, March 23, 2011 - 5:23:34 PM - Greg Robidoux Back To Top (13295)

If you put the database in single user mode you are able to run a DBCC CHECKDB and fix corruption.

You can refer to this MSDN article for more info:


Wednesday, March 23, 2011 - 5:13:44 PM - Ron Back To Top (13294)

The database cannot be repaired using checkdb as mentioned, it is Read-Only. 

get free sql tips
agree to terms