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

SQLServerCentral is supported by Red Gate Software Ltd.

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

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 2:40 AM
Points: 356, Visits: 1,667

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

blog: http://sarveshsingh.com

Twitter: @sarveshsing

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: 55, 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

suspectrecovery

Posted Thursday, August 16, 2012 5:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 5:54 AM
Points: 1, Visits: 0

just mail to me at suspectrecovery@gmail.com, i'll mail SUSpect recovery utility to you.

Post #1345870

GilaMonster

Posted Thursday, August 16, 2012 6:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 38,105, Visits: 30,396

Please note: 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1345873

« Prev Topic | Next Topic »

Permissions