Post reply

How to recover Database from Suspect mode

  • July 9, 2009 at 4:55 am

    #95868

    Hi ,

    One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

    The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.

    Please note other databases in the same server are working fine.

    We tried the following options but with no success.

    1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.

    2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.

    3. Tried dbcc chkdb with repair_allow_data_loss.

    Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

    Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands,

    scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

  • July 9, 2009 at 5:26 am

    #1022270

    Maybe this website can help you with that: http://binaryworld.net/Main/CodeDetail.aspx?CodeId=3847

  • July 9, 2009 at 8:27 am

    #1022400

    In my experience...restore from backup!

    I have never been able to recover a suspect database (unless the server only run out of log space)...

  • July 9, 2009 at 8:35 am

    #1022414

    Recovery method depends on why the DB is suspect in the first place. There are a number of possible causes, with radically different solutions.

    Please can you look through the error log and look for any messages that indicate why the DB has been marked suspect. Post those messages here.

    Do you have a recent backup of this database?

    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

  • September 15, 2010 at 1:28 pm

    #1221630

    Hi,

    Could someone tell me how to recover from a database with the status of 'Suspect' when the log file ran out of space? I'm running on SQL 2000.

  • May 22, 2014 at 4:42 am

    #1715629

    Enter this script in new query window od SQL Server Management Studio

    EXEC sp_resetstatus 'yourDBname';

    ALTER DATABASE yourDBname SET EMERGENCY

    DBCC checkdb('yourDBname')

    ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE yourDBname SET MULTI_USER

    SSMS Expert

  • May 22, 2014 at 4:49 am

    #1715632

    🙂

    SSMS Expert

  • May 22, 2014 at 5:00 am

    #1715637

    Please note: 4 year old thread, and repair allow data loss should never be the first suggestion and should never be suggested without an explanation of the effects.

    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

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

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