Post reply

Recovering a suspect database

  • January 12, 2004 at 2:23 am

    #107695

    Help!

    One of my databases is suspect now. I don't want to lose today transactions. Would you pls tell me how I solve this problem! Thanks a lot. []

  • January 12, 2004 at 6:07 am

    #489590

    Hi,

    If you haven't a recent backup then you can do the following. Look in your logs first, to see if is autorecovering. If nothing is in the logs, run sp_who2 to see if there is a rollback on the db in question. If there is leave it it will 'hopefully' recover.

    If not, and as reset status will not work you'll have to set the db to emergency mode update sysdatabases set status = 32768

    Then set the db to single user

    exec sp_dboption 'yourdb', 'single user', 'true'

    Then run dbcc checkdb ('yourdb', REPAIR_REBUILD)

    Set single user to false.

     Hopefully this will work, it has for me a few times. Good luck!! Ritch

    P.S Don't reboot the server or restart SQL!! 

     

     


    "I didn't do anything it just got complicated" - M Edwards

  • January 12, 2004 at 10:02 am

    #489609

    You also need to be sure that you know why the database went suspect. Check the error log for a reason.

     

    You can clear the suspect flag as well with

    sp_resetstatus <db name>

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 12, 2004 at 2:30 pm

    #489646

    Hi Steve, sp_resetstatus hasn't always worked for me. Had several occassions where I ran sp_resetstatus on a suspect db, which it said was successful. Then tried to set the db to single user but it failed saying the db couldn't be accessed. The db was then marked as suspect again. But when I set the status manually to emergency it worked fine. I could never find an explaination, any ideas why? Cheers Ritch


    "I didn't do anything it just got complicated" - M Edwards

  • January 12, 2004 at 2:37 pm

    #489649

    I would highly suggest you call Product Support Services to resolve this.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 12, 2004 at 2:55 pm

    #489654

    I would agree with above. The suspect flag is just a bit setting in sysdatabases. If it keeps getting reset, then likely the issue has not been resolved and PSS is your best bet at resolving this.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 13, 2004 at 3:15 am

    #489698

    Thanks, the problem was with my old employer.  So guess I'll never know


    "I didn't do anything it just got complicated" - M Edwards

  • January 13, 2004 at 7:32 am

    #489743

    sp_resetstatus requires you to restart the SQL Server service, was that done when it didn't seem to work for you?

  • January 13, 2004 at 8:14 am

    #489752

    Yep, I tried it with restarting and not restarting.    


    "I didn't do anything it just got complicated" - M Edwards

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

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