Can't drop, detach or set SUSPECT database to emergency mode

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » Can't drop, detach or set SUSPECT database to...

Can't drop, detach or set SUSPECT database to emergency mode

Rate Topic

Display Mode

Topic Options

Author

Message

debbie_jacob

Posted Friday, December 11, 2009 7:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 25, 2010 10:08 AM
Points: 9, Visits: 84

I have a database in a dev enviroment that is in SUSPECT mode. This is 2005. I don't need to recover the database. It is just development and the users can restore and/or upgrade from backup. I just want to remove it. I have browsed the forums but have not found a way to do it. Here is what I tried and the messages I received back:

> drop database my_database
Msg 926, Level 14, State 1, Line 1
Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

> EXEC sp_detach_db 'my_database';
Msg 3707, Level 16, State 2, Line 1
Cannot detach a suspect database. It must be repaired or dropped.

> ALTER DATABASE my_database SET EMERGENCY;
Msg 926, Level 14, State 1, Line 1
Database 'my_database' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

I am not sure what else to try. Is there a way to force a db to be dropped?

I can't open/view the errorlog (another issue, perhaps related?). I get an error message saying "Unicode File expected". Our RAID 5 had some issues last week and the systems guy fixed that. There are also 5 dbs whose state is "Recovery_Pending". I mention this in case it is all related and that it might help. I would like to remove these as well but I will tackle one issue at a time. :)

Thanks!

Post #832962

ss-457805

Posted Friday, December 11, 2009 8:12 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 12, 2010 9:45 AM
Points: 262, Visits: 927

Drop database <DBname> should work irrespective of the state.

how to post a question for better assistance.
Tally Tables by Jeff Moden
Cross tabs and pivots by Jeff Moden

Post #832970

debbie_jacob

Posted Friday, December 11, 2009 9:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 25, 2010 10:08 AM
Points: 9, Visits: 84

Ok. I did it by brute force. I stopped Sql Server. Renamed the data file. When I brought Sql Server back up, the database was no longer marked as "suspect" but it could not be accessed either.

I issued EXEC sp_detach_db my_database. It returned with a warning but it appears to have worked. Then I just deleted the .mdf and .log files.

Post #833007

rzoltan

Posted Friday, December 11, 2009 1:58 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 30, 2009 3:36 AM
Points: 51, Visits: 6

Next You have database on SUSPECT and You don't need to recover it, right click it and choose Delete. I did that way several times.

Post #833203

« Prev Topic | Next Topic »

Permissions