Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Using the Emergency State for a Corrupt SQL Server Database

MSSQLTips author Atif Shehzad By:   |   Read Comments (12)   |   Related Tips: More > Disaster Recovery
Problem

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?

Solution

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

CREATE DATABASE ForEmergency
GO

USE ForEmergency
GO

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

INSERT INTO students (ROLLNO, NAME)
SELECT 1,'Atif' UNION ALL
SELECT 2,'Shehzad' UNION ALL
SELECT 3,'Shahid' UNION ALL
SELECT 4,'Mohsin' UNION ALL
SELECT 5,'Asad'
GO

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

SELECT DATABASEPROPERTYEX ('ForEmergency', 'STATUS') AS 'DBStatus'
GO

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
GO

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

ALTER DATABASE ForEmergency SET EMERGENCY
GO

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


Using DBCC CHECKDB

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

ALTER DATABASE ForEmergency SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DBCC CHECKDB (ForEmergency, REPAIR_ALLOW_DATA_LOSS)
GO

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.

ALTER DATABASE ForEmergency SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

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
GO

ALTER DATABASE ForEmergency SET OFFLINE -- put the database offline
GO

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


Last Update: 3/23/2011


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, March 23, 2011 - 5:13:44 PM - Ron Read The Tip

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


Wednesday, March 23, 2011 - 5:23:34 PM - Greg Robidoux Read The Tip

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: http://msdn.microsoft.com/en-us/library/ms176064.aspx

 


Thursday, March 24, 2011 - 12:43:43 AM - Ahmad Read The Tip

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!

 

 

 

 


Thursday, March 24, 2011 - 1:25:17 AM - Atif Shehzad Read The Tip

@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 - 1:40:03 AM - Ahmad Read The Tip

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 - 2:08:34 AM - Atif Shehzad Read The Tip

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


Thursday, March 24, 2011 - 2:32:02 AM - Ahmad Read The Tip

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

 

Thanks


Friday, March 25, 2011 - 1:13:35 AM - Atif Shehzad Read The Tip

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

Thanks


Wednesday, January 25, 2012 - 11:16:34 AM - Joshua Guttman Read The Tip

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


Monday, October 29, 2012 - 1:25:58 PM - Shahid Read The Tip

Atif Bhai,

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


Tuesday, November 05, 2013 - 12:24:41 PM - sultana Read The Tip

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  

 

 


Tuesday, November 19, 2013 - 3:15:50 AM - Atif Read The Tip

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

http://www.mssqltips.com/sqlservertip/2571/restoring-sql-server-system-databases-msdb-and-model/

 

Thanks



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.