Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005

    Unanswered: SQL 2000: How to recover database in SUSPECT state.


    I've my database, testdb, ended up in Suspect state. The SQL log shows " I/O error 38(Reached the end of the file.) detected during read at offset xxxxxxxxxx in file '<path>\testdb_Data.MDF'" during recovery. I do not have backup to restore the database from. So to run DBCC CHECKDB, I tried to put the database in emergency(bypass recovery) mode using

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

    DBCC CHECKDB showed some allocation and consistency errors and suggested "repair_allow_data_loss" as minimum repair level.

    Now to run

    DBCC CHECKDB('testdb', repair_allow_data_loss)

    I've to put database in SINGLE USER mode. For that I started SQL server by command

    sqlservr.exe -c -m

    Now when I try to run DBCC CHECKDB with repair option it says "Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is in BYPASS RECOVERY mode."

    So it seems I need to change the status of database such that it will allow me to repair it. If I try to reset the status, the database again goes in Suspect state and it seems DBCC commands don't run on database in Suspect state. Does anybody know how to recover the database in this state? Is there any other way to repair it?

    Thanks in advance,

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    If you put a database into bypass recovery mode, it probably isn't worth trying to repair. I'd strongly suggest that you simply copy the data out of that database into a new one, vigorously check the logical consistancy of the tables (are all FK values valid, are any rows obviously missing, does the data survive basic business integrity checks, etc), then discard the original database.

    Trying to fix one of those is a lot like trying to fix a bus after it goes off a cliff... It can be done, it can be made to look good if you are willing to work hard enough, but it will never be "right" again.


  3. #3
    Join Date
    Oct 2003
    Agree with Pat. I fyou have a good backup, restore.

  4. #4
    Join Date
    Jan 2005
    I'm relatively new to SQL. Can you please provide me steps for copy the data out of this database to new one?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts