Post reply

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

  • December 11, 2009 at 7:57 am

    #217566

    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

    #1091209

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

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 11, 2009 at 9:06 am

    #1091239

    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

    #1091410

    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

    #1526502

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

  • August 16, 2012 at 6:01 am

    #1526504

    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

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

  • January 4, 2014 at 2:16 am

    #1677493

    am als having the same problem

  • January 4, 2014 at 12:59 pm

    #1677552

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply