How to Recover MSSQL from "Suspect Mode" / "Emergency Mode" / "Error 1813"

Tue, 09/11/2001 - 19:01 — Andriy Gerasika

This article describes the course of actions you should take

if your database somehow got "suspect mode" or "emergency mode";
if you receive the error 1813 on attaching database

The History Behind

I've been developing under SQL 7 for a while. Sometimes transaction log grew too large. My usual fix for that problem was:

stop the server (on the development machine we do not need any logs),
delete transaction log
start the server again
after that the server was creating empty 1MB transaction log from scratch

I had been using this technique a lot of times under SQL 7, until sometime I have migrated to MSSQL 2000...

After my transaction log grew too large, I stopped the server, deleted the log file and started the server once again. However this time it did not worked: my database got "suspect". I was not able to read or write from the database. A week of work seemed to be lost... Ouch!...

I searched the newsgroups - a lot of people were facing the same problem and nobody knew the solution. All I found was the article: http://support.microsoft.com/support/kb/articles/q251/6/28.asp. I have tried that and revealed information in the article applies only to SQL 7 and does not to SQL 2000...

Did I solved the problem? Read on!

"Suspect Mode" --> "Emergency Mode"

Open Enterprise Manager,

Right click onto your server node, - an SQL Server Properties dialog will popup,
Enable "Allow modifications to be made directly to the system catalogs" option
Now proceed to the sysdatabases table in the master database, locate your database row and put 32768 into its status column
Restart SQL Server and refresh Enterprise Manager

Open Enterprise Manager - the database will be marked as "Emergency Mode"

These articles explain the trick behind suspect and emergency mode:
http://support.microsoft.com/support/kb/articles/Q165/9/18.ASP
http://www.swynk.com/friends/knight/unmarksuspect.asp
http://support.microsoft.com/support/kb/articles/Q180/5/00.asp

"Emergency Mode" --> "Normal Mode"

Now your database is in "emergency mode". Disregard Enterprise Manager does not show tables, views and procedures - use Query Analyzer's Object Browser instead.

"Emergency mode" means database is locked against any modifications, it is readonly forever. There is no way to bring "emergency" database back to normal state. All we can do is copy all data from it into new database.

Create new database (pubz)
Create new DTS package
Use "Copy SQL Objects" task to transfer data from old database to new database (pubs->pubz)
Execute the DTS job & enjoy!

Note that there might be complex dependencies between database objects, so Copy SQL Objects task might not work properly. For example: view A is dependent on view Z; Copy SQL Objects task will try to create view A first, -- that will fail, because view Z does not exist yet.

"Error 1813" --> "Suspect Mode"

If you receive "Error 1813", this probably means that you are trying to attach database you have improperly detached, or haven't detached at all. Only successfully detached databases can be successfully attached back.

Create new database using the same name and file location as database that is failing to attach
Stop SQL Server
Copy non-attachable data files over the new ones
Delete log files
Start SQL Server

Open Enterprise Manager - the database will be marked as "suspect"

Once your database is "suspect", use instructions outlined above...

Note that database will be marked "suspect" only if transaction log contained active sections. To illustrate the problem for 100%, run a few inserts against the database before deleting transaction log.

Previous post
« How to Make Delphi TPanel designable like TForm & TDataModule

Next post
How to Optimize Delphi Application Performance to the Max using FastCode Library »

MSSQL
HowTo

Trackback URL

Comments

Thu, 02/07/2008 - 16:45 — Ron

You just saved me countless

You just saved me countless hours of work! THANKS!

Sat, 01/29/2011 - 08:41 — Sireesha

Good

Wed, 02/13/2008 - 17:05 — fsilber

I am trying to recover

I am trying to recover stored procedures and table definitions from a development database. I don't care about the data.

I have a back-up that is nearly three years old; I was thinking of recovering scripts for the object definitions and then re-applying them to the backed-up database. But Query Analyzer's object browser does not show me the dates of object creation. How will I identify which objects have changed if I cannot view them in Enterprise Manager?

Or should I simply rename or drop the EMERGENCY MODE database and then rename my new database to the old name? (Can a database in EMERGENCY MODE be renamed?) I don't want to lose the EMERGENCY MODE database -- just in case a dependency prevented an object from being copied by my DTS package.

Fri, 09/19/2008 - 01:24 — Pel

Hi there, Just wanted to

Hi there,

Just wanted to remark that the info on your page did lead to a resolution for the problem, but in this case your 'endloesung' would have meant rewriting and distributing software with hardcoded routines, etc.

the following SQL script will restore the '0' or normal state on the database

use master

update sysdatabases set status = 0
where name like '<databaseinrecmodename>'

Tue, 10/14/2008 - 15:56 — Anonymous

Great information. You were

Great information. You were an immense help in solving my problem!

Sat, 12/06/2008 - 10:37 — Anonymous

Good keep it up good its

Good keep it up good its given a perfect solution for me

Tue, 02/17/2009 - 13:07 — David Pearson

Thanks so much for this. It

Thanks so much for this. It probably saved my job!!!

Sat, 06/06/2009 - 22:09 — Anonymous

great work

for two days i've been researching this issue and your post is the best so far.. thx

Wed, 07/22/2009 - 07:42 — Anonymous

Database Suspect

Great information

Thanks very much

Tue, 08/25/2009 - 01:19 — Anonymous

it worked! thx a bundle!

Thu, 09/03/2009 - 07:30 — Anonymous

Suspect mode

Dear ,
plz help me to recover a mdf data from suspect / emergency mode
I m using sql 2000 i dont hv any backup
i m not able to recollect data
so plz help me to same

Thanks & Regards ,
Vipin jha
vipin_jha123@yahoo.co.in

Fri, 09/25/2009 - 00:32 — Andriy Gerasika

Answer the mail or write to

Answer the mail or write to andriy.gerasika@gerixsoft.com

Thu, 02/25/2010 - 16:07 — Lalo from SLP

it worked!

Thanks for this post!!! I followed it and worked very well!!

Tue, 04/13/2010 - 16:57 — subodh

thanksssssssssssssss

Fri, 06/25/2010 - 22:19 — Andy

foreign keys?

Is there a way around the foreign key restriction problem you described at the end? If I could just make sql not check for foriegn keys while I do this, I think that would do it.

Sun, 06/27/2010 - 14:44 — Andriy Gerasika

You can try "script database"

You can try "script database" task, or

use master
update sysdatabases set status = 0
where name like '<databaseinrecmodename>'

from one of the comments above

Wed, 08/11/2010 - 15:17 — Marc Paas

MSSQL "suspect Mode"

Hello everybody,

the information <recovering MSSQL from 'suspect Mode'>
helps us to restore a important database. So, we saved a lot of money.
2039 tables of 2400 were rescued with the complete data.
We want to say, thank your for it. Thank you very much.

In that case a donation is normal. Please tell us your bank account...

nice regards

Marc

Wed, 08/11/2010 - 19:34 — Andriy Gerasika

Thanks :) As a donation, you

Thanks :) As a donation, you may purchase IndieVolume license from http://www.indievolume.com

Tue, 03/08/2011 - 11:37 — Kiran

recovering emergence mode

its not working for me

Tue, 03/08/2011 - 11:47 — Andriy Gerasika

what exactly step is not

what exactly step is not working? Is it MSSQL 2000 or are you trying to recover >=MSSQL 2005?

Tue, 03/08/2011 - 13:08 — Kiran

DB in Emergence/Recovery mode

This is what the error im getting when i try to runn any query

Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 0 in database 'DBNAME'.

Connection Broken

Please help in this regard

Tue, 03/08/2011 - 13:37 — Andriy Gerasika

Do you have transaction log?

Do you have transaction log? Try to delete it

Mon, 04/25/2011 - 08:45 — Ramin

data copy faild

Hi dear
when I create DTS and wanted to copy data from corrupted database to new one , i got the error that mean it can't copy data because
[Microsoft][ODBC SQL Server Driver][SQL Server]'iranet.net\r_rostamy' is not a valid name because it contains invalid characters... i used windows authentication ..and from remote desktop did it. I always use windows authentication and my database doesn't have SQL server pass.... would you help me with this issue please? I'm struggleing this problem for days..... :-(

thank you in advanced
Ramin

Tue, 04/26/2011 - 10:24 — Andriy Gerasika

I guess it is because of dot

I guess it is because of dot "." in domain name: iranet.net

try using regular MSSQL authentication

Thu, 04/28/2011 - 10:56 — Ramin

thank you so much

I just wanted tom say thank you buddy...your solution was so useful be welllllllllllllllllllllllllllllllllllll :-*

Mon, 04/25/2011 - 08:59 — Ramin

sorry, i did it local (i mean not from remote desktop) but it got that error again....
thank you

Fri, 05/06/2011 - 15:22 — Tejas

thanks

Thanks .

Your methods saved my hours.

Mon, 08/15/2011 - 16:28 — Anonymous

I don't know waht to say

Thank you very much...........
Very nice Site.

Wed, 01/04/2012 - 18:48 — Joel

you're the best

you save my client 7 year old database, its very important for my client, it was with sql server 7.0, I almost lose hope and I pray that God will help me via other people, and that is YOU. You're the best, thank you very much. God Bless.

GerixSoft