locked
How to repair a corrupt database

    Question

  • Dear all,

    I have a big issue with a SQL Server 2008 corrupt database:

    DBCC checkdb (<my db name>)

    RESULT:

    Msg 8967, Level 16, State 218, Line 1

    An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.

    Msg 7985, Level 16, State 2, Line 1

    System table pre-checks: Object ID 7. Could not read and latch page (1:3523) with latch type SH. Check statement terminated due to unrepairable error.

    DBCC results for 'SboBettiniBAD'.
    Msg 8944, Level 16, State 12, Line 1
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 458752 (type Unknown), page (1:3523), row 13. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 43240 and 539.
    CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
    CHECKDB found 0 allocation errors and 1 consistency errors in database 'SboBettiniBAD'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SboBettiniBAD).

    ...........................

    It seems the error is in the a systemtable: object_id = 7 = sysallocunits

    I already tried with dbcc checkdb command with repair_allow_data_loss parameter but I have the same error.

    Does it exist a free SQL repair tool or command?

    Best regards

         Emanuele

    Wednesday, February 8, 2012 3:37 PM

Answers

  • Emanuele,

    Most corrupt databases are due to a malfunctioning storage system or disk array.  Correcting corruption "in place" often leads to more data corruption since the root hardware cause does not get fixed.  I would try and use some other storage system or even a different server to attempt system repairs.  I strongly recommend you test and fix or replace your storage system before trying to rely on it for critical data storage.

    Once the hardware is dependable, then you can start trying to repair the data.

    Please read the posts in database corruption and repair here:

    http://sqlskills.com/BLOGS/PAUL/category/Corruption.aspx

    before you risk losing all your data.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Wednesday, February 8, 2012 6:03 PM
    Moderator
  • http://Support.microsoft.com

    Scroll down to the "Get Help Now" link.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Thursday, February 9, 2012 6:16 PM
    Moderator
  • Database has been repaired using Stellar Phoenix SQL repair tool

    Best regards

        Emanuele

    • Marked as answer by Emanuele Croci Wednesday, October 17, 2012 2:13 PM
    Wednesday, October 17, 2012 2:13 PM

All replies

  • Emanuele,

    Most corrupt databases are due to a malfunctioning storage system or disk array.  Correcting corruption "in place" often leads to more data corruption since the root hardware cause does not get fixed.  I would try and use some other storage system or even a different server to attempt system repairs.  I strongly recommend you test and fix or replace your storage system before trying to rely on it for critical data storage.

    Once the hardware is dependable, then you can start trying to repair the data.

    Please read the posts in database corruption and repair here:

    http://sqlskills.com/BLOGS/PAUL/category/Corruption.aspx

    before you risk losing all your data.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Wednesday, February 8, 2012 6:03 PM
    Moderator
  • It appears you may have an unrecoverable error. You should without delay :-

    Backup the tail log (perform a log backup).

    Then :-

    Refer to your backups and ideally attempt a test restore on another system

    Restore last good full with norecovery

    All subsequent log backups with norecovery

    applying the tail log backup with reovery

    If this works then you can look to restoring on your live system.

    As Geoff touches on you should also consider how the corruption occurred in the first place.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|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, February 8, 2012 9:08 PM
  • Dear all,

    thank you for your help.

    I run this command:

    dbcc CHECKCATALOG(<my db name>) WITH NO_INFOMSGS

    Msg 8967, Level 16, State 218, Line 1

    An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.

    Msg 5233, Level 16, State 55, Line 1

    Table error: alloc unit ID 458752, page (1:3523). The test (dbccAuditRecSucceeded == TRUE) failed. The values are 13 and 7411.

    Msg 7985, Level 16, State 2, Line 1

    System table pre-checks: Object ID 7. Could not read and latch page (1:3523) with latch type SH. Check statement terminated due to unrepairable error.

    I haven't any old not-corrupt db backups.

    Our IT employee didn't find any error on the harddisk.

    I also restored the backup of corrupt database on another machine, but the error is still present.

    I hope someone can help me...

    Best regards
         Emanuele

    Thursday, February 9, 2012 8:34 AM
  • http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-bug-that-people-are-hitting-Msg-8967-Level-16-State-216.aspx

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, February 9, 2012 8:57 AM
  • SQL server generates error Msg 8967 when DBCC CheckDB is fail to perform consistency check on the database. This could be happen due to several reasons like metadata inconsistencies or database snapshot corruption. To examine more, see the different state in error message.

    To solve error 8967, use TABLOCK hint with DBCC CheckDB command.
    http://support.microsoft.com/kb/960791

    It looks like you have some serious database corruption in one of important system table. My guess is that you can't restore your database without a healthy backup. You can open a case with Microsoft Support.


    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

    Thursday, February 9, 2012 9:06 AM
  • Dear all,

    do you know I can open a request to the Microsoft Support?

    Do you have the link, please?

    Best regards

       Emanuele

    Thursday, February 9, 2012 4:23 PM
  • http://Support.microsoft.com

    Scroll down to the "Get Help Now" link.


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Thursday, February 9, 2012 6:16 PM
    Moderator
  • I recommend NOT to repair a corrupt database, as many others said before, data corruption often hides hardware anomalies or glitches.

    Check your I/O hardware, try to backup your latest log then restore from a healthy backup.

    Good luck!


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 17, 2012 1:08 PM
  • Database has been repaired using Stellar Phoenix SQL repair tool

    Best regards

        Emanuele

    • Marked as answer by Emanuele Croci Wednesday, October 17, 2012 2:13 PM
    Wednesday, October 17, 2012 2:13 PM
  • Agree with Sebastian but in the absence of healthy backup; users have no any other option to restore up-to-date data. At this scenarios SQL repair tool can be helpful. 

    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

    Tuesday, October 23, 2012 5:10 AM
  • I can say that there are no free tools available on the market that can help you with this (there are some tools with free trial, so maybe you can try them out).

    However if your database was in full recovery model and if your LDF (transaction log) file isn’t damaged it may be possible to perform full recovery by reading this .ldf file.

    You can use undocumented functions, such as DBCC LOG and fn_dblog, but these are not easy (especially if you’ve never used them before). Here is the MSDN thread on this topic.

    If you are not familiar with these functions, than I suggest using commercial tools for this purpose, like the SQL transaction log reader from ApexSQL, or SQL Log rescue from Red Gate (free but SQL Server 2000 only).
    Wednesday, October 16, 2013 7:59 AM
  • This forum is not for marketing place and We dont expect people to keep on recommend on a particular product. If you are good with that then it's fine. But in this thread I could see lot of promotions for the product.

    I'm going to lock this thread. If you problem got resolved then it's fine, if you are still facing issues please get in touch with Microsoft support where you can get a proper guidance.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Tuesday, January 7, 2014 9:57 AM
    Moderator