Possible to repair or drop one damaged table

  • We have a database that was put in 'suspect' mode, it is now in 'emergency' mode; using DBCC CHECKDB the problem table was identified.

    I tried using DBCC CHECKTABLE ( 'MyTable', REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS

    but it was not allowed

    I've had messages that it needs to be in single user mode, which I then altered the database to single_user mode but since it is still in emergency mode it ignores the single user mode.

    I cannot drop or truncate the table --

    it will not let me drop the primary key constraint and clustered index to rebuild that way either.

    Does anyone know any way to get around this? I hate to have to rebuild the whole database just because of one bad table.

    Thank you in advance for your assistance.

  • If the database is in emergency mode, you have to run CheckDB with the repair_allow_data_loss option to have a chance of bringing it back online. CheckTable is not sufficient. That or restore from backup.

    Do you know how much data you're going to lose in this case?

    Can you run the following, before trying a repair, and post the full and complete output?

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    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
  • Nothing like stating the obvious. I had used the checkdb and it identified that the only object with a problem was the one table. so I thought I would just "fix" the one table. Although I have to say why will it let me run checkdb with repair but not checktable with repair?

    If I loose any data from this table it will be okay. It happens to have specific historical data from 2003 that I can restore from a development copy of this database.

    I'm running checkdb with repair while typing this reply.

    THANK YOU MUCH.

  • Ellen-477471 (7/8/2010)


    Although I have to say why will it let me run checkdb with repair but not checktable with repair?

    Because SQL knows that the database is damaged and needs repairing before it can be brought online. You may know that there's only one table, but the SQL repair and consistency requires that the entire database be repaired and all damage repaired before bringing the DB online.

    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
  • Received the same message:

    DBCC CHECKDB

    ( 'MyDatabase' , REPAIR_ALLOW_DATA_LOSS

    ) WITH ALL_ERRORMSGS

    Server: Msg 3908, Level 16, State 1, Line 1

    Could not run BEGIN TRANSACTION in database 'MyDatabase' because the database is in bypass recovery mode.

    Do you know a way to get around this? I am not sure why it is trying to use a 'begin transaction'

    I know it is NOT possible to rollback the repair --- so I don't get it.

  • I've seen that once before, when I was playing with corruption. Happened when there was an active transaction when the DB went suspect. Not sure why.

    As far as I know, there's no way around it. Emergency mode repair is the absolute, final last resort. If it fails, there's no alternative, nothing else that can be done.

    I hope you have a backup. If not and the DB is still readable, export the data, script the objects and recreate the DB.

    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
  • I have a really weird situation. I have a database with one table with torn pages, but the database is not suspect. The table is not in use (I think someone was doing some temp work and broke something) I cant BCP out the table. DBCC shows that this is the only object affected. Can I simply drop the table or will I risk the entire DB going suspect by so doing? its a 2tb db, and I haven't tried dropping a "bad" table before. I really don't want this to go suspect. We run weekly DBCC Checks and that's what picked up this table error. We cant go back to backups to an entire week, its out of the question, and worst case scenario, we migrate every table in the DB except for this one bad one and fix it that way, or run DBCC repair allow data loss. The Users want to avoid any outage if at all possible.

    My thinking is, when dropping a table, doesn't that just affect the sysobjects/sysindexes tables, and return all the deleted pages back to the DB without scanning the table data? will this fix corruption in a single table or cause More damage? We can still read the table as long as we don't encounter one of the torn pages.

  • Please post new questions in a new thread and give as much information as possible, including errors and the output of CheckDB.

    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