December 11, 2009 at 7:57 am
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!
December 11, 2009 at 8:12 am
Drop database <DBname> should work irrespective of the state.
December 11, 2009 at 9:06 am
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.
December 11, 2009 at 1:58 pm
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.
August 16, 2012 at 5:55 am
just mail to me at suspectrecovery@gmail.com, i'll mail SUSpect recovery utility to you.
August 16, 2012 at 6:01 am
Please note: 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2014 at 2:16 am
am als having the same problem
January 4, 2014 at 12:59 pm
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply