Post reply

DBCC CHECKDB ( , REPAIR_ALLOW_DATA_LOSS) is not fixing Db

  • July 1, 2017 at 3:01 pm

    #338646

    Dear All,

    I got following errors in one of the db

    Msg 8939, Level 16, State 98, Line 8
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594160939008 (type Unknown), page (1:145584). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
    Msg 8998, Level 16, State 2, Line 8
    Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:145584) to (1:153671). See other errors for cause.
    Msg 8939, Level 16, State 98, Line 8
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 6989699873004060672 (type Unknown), page (1:153672). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -10.
    Msg 8998, Level 16, State 2, Line 8
    Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:153672) to (1:161759). See other errors for cause.
    Msg 8939, Level 16, State 98, Line 8
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594160939008 (type Unknown), page (1:145584). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
    CHECKDB found 4 allocation errors and 1 consistency errors not associated with any single object.

    I did run DBCC CHECKDB (<db>, REPAIR_ALLOW_DATA_LOSS). It didn't fix error. As a next best option, I tried restoring available backups. It seems all my backups are also having the same issue. I had to check for this error when log was found to be growing very large. In short, I do not have a clean backup now :(. Is there any way I can fix this error.

    Thanks.

  • July 1, 2017 at 4:06 pm

    #1949060

    Without a clean backup, that's not fixable. checkDB is not guaranteed to fix all problems, that's what a good backup strategy is there for (one of the things)
    Restore from backup should be the first, best repair for corruption. CheckDB is the last resort, not the first.

    Since you have no clean backups
    Script all objects
    Export data (DO NOT script data), and hope that most of it will script out. Some may not.
    Recreate the database.

    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

  • July 3, 2017 at 8:04 am

    #1949146

    Restoring from backup is the only option for corruption on PFS tables. As we don't have a clean backup, we won't be able to fix the corruption

Viewing 3 posts - 1 through 2 (of 2 total)

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