locked
How to recover SQL Server database from suspect status RRS feed

  • Question

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


    Pls note other databases in the same server are working fine.
    Later on more space was made available on this disk. 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).

    Thursday, May 24, 2007 11:52 AM

Answers

All replies

  • Did you first run a checkdb? What were the errors from the checkdb?

    What errors are in the SQL Server error log? Do you have backups available to restore from? That's a better option if the checkdb reported allow_data_loss was the repair level required.

     

    -Sue

    • Proposed as answer by manish_anish Wednesday, January 13, 2010 4:59 AM
    Friday, May 25, 2007 3:53 AM
  • Thanks for the response. The problem is resolved. We brought the database in emergency mode and then in single user mode. Later we were able to recover the database using the checkdb utility, but with some loss of data.

     

    Friday, May 25, 2007 12:21 PM
  • how to run checkdb?

    • Proposed as answer by manish_anish Wednesday, January 13, 2010 4:58 AM
    Sunday, December 16, 2007 4:15 AM
  • -- How to run checkdb

    dbcc

    checkdb

    Wednesday, September 21, 2011 6:27 AM
  • @Joseph, Its a very old discussion but you can read this article

    http://www.sql-server-pro.com/dbcc-checkdb.html. It may help you.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
    Wednesday, September 21, 2011 6:44 AM
  • Run This Query on Master Database

    sp_configure "Allow update", 1

    go

    reconfigure with override

    go

    update sysdatabases set status = 32768 where name = 'distributor'

    go

    sp_configure "Allow update", 0
    go

    reconfigure with override

    DBCC CHECKDB ('databasename' /*,REPAIR_REBUILD*/)
    WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

    Friday, May 9, 2014 1:38 PM