Post reply

Suspect Database

  • September 7, 2004 at 2:51 am

    #83964

    Hi Guys,

    I have a database that has been marked as suspect (shown in Enterprise Manager) due to some jobs failing as a result of lack of disk space.

    Is my only option to restore from last backup and re-run the jobs that failed?

    Or can the DB be revived?

    Cheers

    Graeme

  • September 7, 2004 at 3:21 am

    #521928

    The ideal way to "unsuspect" your database is running sp_resetstatus against it. Immediatly after that, stop/restart your instance. Instead of this restart, you can try a detach/reattach of the database (It MAY work, but may not...).

    Have you tried a simple RESTORE DATABASE <db_name> WITH RECOVERY ?

     

  • September 7, 2004 at 3:43 am

    #521930

    Yeah, we tried that.

    Sysdatabases was not showing the database as suspect although Enterprise Manager IS showing the suspect tag.

    Trying the Stop/Start, detach/attach route now.

    Thanks for that.

    Graeme

  • September 7, 2004 at 4:56 am

    #521934

    The best resource to use is sp_resetstatus against the "suspect" database.

    I encountered this problem earlier and sp_resetstatus was able to solve that.

    Sekhar

     

  • September 7, 2004 at 12:12 pm

    #522016

    if "sp_resetstatus " doesn't work, I dont' recommend to do detach/attach route. I've done several times w/ testing environment and when it doesn't work, you have almost zero chance to recover.

    MSFT PSS team said, no matter what, they have better chance to recover when the database is attached rather then detached even though they are corrupted.

    If it is very imprant database, try rebuild the database if you can access the data by using bcp. Also make sure you have good backup in hand.

  • September 8, 2004 at 1:16 am

    #522058

    Nice one,

    I shall remember that.

    I stopped/started the service and the database came back on line.

    Fortunately this was only a test environment but it is certainly worth going through the options.

    Thanks again guys.

    Graeme

     

  • September 8, 2004 at 3:30 am

    #522075

    This seems to be a very common problem I am facing the same problem twice in the production enviornment.

    I agree with 'Journeyman' that you should not detech/attach the suspected database. Cause its recoverable in most of the cases, its depend on the type and magnitude of destrection to your DB.  

    At the worst possible scenerio you could put the database in Emergency mode and can transfer to object to  a new database through DTS/Bcp utlity.

     

  • September 8, 2004 at 3:32 am

    #522076

    Having investigated this I have to agree with you.

  • September 9, 2004 at 8:25 am

    #522324

    I just had to throw this out

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q165918

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

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