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

  • debbie_jacob

    Old Hand

    Points: 371

    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!

  • ss-457805

    SSCertifiable

    Points: 5872

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

  • debbie_jacob

    Old Hand

    Points: 371

    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.

  • rzoltan

    SSC Enthusiast

    Points: 103

    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.

  • suspectrecovery

    SSC Rookie

    Points: 43

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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • vinod.s81

    SSC Rookie

    Points: 30

    am als having the same problem

  • Jeff Moden

    SSC Guru

    Points: 993645

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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