Post reply

DBCC Consistency Errors - Any help appreciated

  • January 14, 2011 at 3:30 am

    #230245

    Hi,

    I have been trying to troubleshoot some consistency errors in one of our main production databases. I have managed to narrow down the issue to a specific table. Or at least, I think I have - dbo.Attach

    I have put the database into single user mode, and run dbcc checkdb repair_rebuild, but from what I can see, the results all say 'Repairing this error requires other errors to be corrected first'

    Can anyone help me narrow down what needs fixing first, or how to repair this db?

    I have attached the output of dbcc.

    Thank you,

    Bodsda

  • January 14, 2011 at 8:18 am

    #1273574

    Please run the following and post the full and complete results

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

    Do you have a clean backup (one without corruption)?

    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

  • January 19, 2011 at 2:56 am

    #1275148

    GilaMonster (1/14/2011)

    Please run the following and post the full and complete results

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

    Do you have a clean backup (one without corruption)?

    Output attached.

    I took over this system after the corruption occurred, I do have a clean backup on tape from a few months ago, but this particular table holds file attachments for a call logging system so we would lose a fair amount of data if I had to restore.

    I noticed that the output says it will only show the first 1000 errors. If you need more than what is in the output, please let me know how to get it.

    Thanks,

    Bodsda

  • January 19, 2011 at 3:24 am

    #1275168

    I'm going to see the whole lot. Though, from the part you posted, it doesn't look like it's repairable.

    To get the entire output, open a command window (Start-> Run-> cmd.exe) and run the following (replacing <Servername> with the name of the server and <Database name> with the name of the database.) If this is a named instance, the command is slightly different. Let me know if that is the case and I'll revise the command.

    sqlcmd -S <ServerName> -E -Q"DBCC CHECKDB('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS" -o"Output.txt"

    Then zip the output file (It's going to be large and I refuse to download large attachments) and attach to a post. The 1000 error limit is a Management Studio limit, hence why we're using SQLCMD to get the whole lot.

    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

  • January 19, 2011 at 5:23 am

    #1275219

    GilaMonster (1/19/2011)

    I'm going to see the whole lot. Though, from the part you posted, it doesn't look like it's repairable.

    To get the entire output, open a command window (Start-> Run-> cmd.exe) and run the following (replacing <Servername> with the name of the server and <Database name> with the name of the database.) If this is a named instance, the command is slightly different. Let me know if that is the case and I'll revise the command.

    sqlcmd -S <ServerName> -E -Q"DBCC CHECKDB('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS" -o"Output.txt"

    Then zip the output file (It's going to be large and I refuse to download large attachments) and attach to a post. The 1000 error limit is a Management Studio limit, hence why we're using SQLCMD to get the whole lot.

    Here is the output from the sqlcmd command

    Thanks for all your help so far GilaMonster,

    Bodsda

  • January 19, 2011 at 5:32 am

    #1275224

    The corruption you have is not repairable.

    You have two options.

    1) Restore from your last clean backup

    2) Script all objects, export what data you can (some will almost certainly fail) and recreate the DB.

    What you can do to alleviate the data loss from the restore is rename the existing corrupt DB, restore from backup then use some tool like SQLDataCompare (Redgate) to attempt to synchronise. You'll likely have problems and errors because of the corruption.

    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

  • January 19, 2011 at 7:02 am

    #1275278

    GilaMonster (1/19/2011)

    The corruption you have is not repairable.

    You have two options.

    1) Restore from your last clean backup

    2) Script all objects, export what data you can (some will almost certainly fail) and recreate the DB.

    What you can do to alleviate the data loss from the restore is rename the existing corrupt DB, restore from backup then use some tool like SQLDataCompare (Redgate) to attempt to synchronise. You'll likely have problems and errors because of the corruption.

    Thanks for taking the time to look GilaMonster.

    Any data I can salvage will be a good thing. I havent done any form of data exports in the past though, would you be able to point me in the direction of some good documentation?

    When I try to open the offending table, it brings back the first 350 rows or so, and then errors. I'll see if SQL DataCompare can help, cheers 🙂

    Bodsda

  • January 19, 2011 at 7:07 am

    #1275284

    Not talking about anything fancy here, just select statements that have small enough ranges (probably of the clustered index) so that they don't hit the corruption. You will not be able to query the entire table, because of all the damage.

    Do note you could easily spend weeks trying to get data out of this and get very little.

    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

  • January 24, 2011 at 4:02 pm

    #1277511

    Hi Bodsda ,

    Hope you have the backup already. Please try with DBCC CHECKDB with repair_allow_data_loss. Hope this will help to resolve.

    Regards

    Moosa

  • January 24, 2011 at 4:09 pm

    #1277518

    moosamca (1/24/2011)

    Please try with DBCC CHECKDB with repair_allow_data_loss. Hope this will help to resolve.

    It won't, the damage is irreparable. The give-away indication that it's not repairable is the lack of a 'minimum level to repair' message at the end of the checkdb output.

    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

  • January 24, 2011 at 4:16 pm

    #1277521

    moosamca (1/24/2011)

    Hi Bodsda ,

    Hope you have the backup already. Please try with DBCC CHECKDB with repair_allow_data_loss. Hope this will help to resolve.

    Regards

    Moosa

    It's great that you're enthusiastic - but please don't post advice when you don't know what you're talking about.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • January 25, 2011 at 1:39 am

    #1277656

    Hi Moosamca

    If you are interested in learning about corruption and appropriate repairs, have a look at this article. Not comprehensive, but a good place to start: http://www.sqlservercentral.com/articles/Corruption/65804/

    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

  • January 25, 2011 at 1:18 pm

    #1278152

    Thanks Gila

Viewing 13 posts - 1 through 12 (of 12 total)

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