Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can't drop, detach or set SUSPECT database to emergency mode Expand / Collapse
Author
Message
Posted Friday, December 11, 2009 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, December 11, 2009 8:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 357, Visits: 1,683
Drop database <DBname> should work irrespective of the state.

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #832970
Posted Friday, December 11, 2009 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, December 11, 2009 1:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, August 16, 2012 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, August 16, 2012 6:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 42,476, Visits: 35,544
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
Posted Saturday, January 4, 2014 2:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 23, 2014 2:21 AM
Points: 2, Visits: 4
am als having the same problem
Post #1527790
Posted Saturday, January 4, 2014 12:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
vinod.s81 (1/4/2014)
am als having the same problem


What have you tried? Did you try any of the solutions on this thread? Did you try any other solutions? What have you tried?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse