Corruption reported...but index rebuild resolves it

  • john.round

    SSC Veteran

    Points: 232

    On one of our databases some corruption has been detected...sadly it is going back so long we don't have the backup s

    Msg 8909, Level 16, State 1, Line 1
    Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (3:1382245) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
    CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
    Msg 8978, Level 16, State 1, Line 1
    Table error: Object ID 50099219, index ID 4, partition ID 72057611131551744, alloc unit ID 72057611135418368 (type In-row data). Page (1:379520) is missing a reference from previous page (4:28759). Possible chain linkage problem.
    Msg 8928, Level 16, State 1, Line 1
    Object ID 50099219, index ID 4, partition ID 72057611131551744, alloc unit ID 72057611135418368 (type In-row data): Page (4:28759) could not be processed. See other errors for details.
    Msg 8939, Level 16, State 98, Line 1
    Table error: Object ID 50099219, index ID 4, partition ID 72057611131551744, alloc unit ID 72057611135418368 (type In-row data), page (4:28759). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
    Msg 8976, Level 16, State 1, Line 1
    Table error: Object ID 50099219, index ID 4, partition ID 72057611131551744, alloc unit ID 72057611135418368 (type In-row data). Page (4:28759) was not seen in the scan although its parent (3:891503) and previous (4:28758) refer to it. Check any previous errors.
    CHECKDB found 0 allocation errors and 4 consistency errors in table 'tciPatientMOA' (object ID 50099219).
    Msg 8928, Level 16, State 1, Line 1
    Object ID 306100131, index ID 2, partition ID 72057611122180096, alloc unit ID 72057611126046720 (type In-row data): Page (3:1382245) could not be processed. See other errors for details.
    Msg 8976, Level 16, State 1, Line 1
    Table error: Object ID 306100131, index ID 2, partition ID 72057611122180096, alloc unit ID 72057611126046720 (type In-row data). Page (3:1382245) was not seen in the scan although its parent (3:718661) and previous (3:1382244) refer to it. Check any previous errors.
    Msg 8978, Level 16, State 1, Line 1
    Table error: Object ID 306100131, index ID 2, partition ID 72057611122180096, alloc unit ID 72057611126046720 (type In-row data). Page (3:1382246) is missing a reference from previous page (3:1382245). Possible chain linkage problem.
    CHECKDB found 0 allocation errors and 3 consistency errors in table 'tciAdminEvent' (object ID 306100131).
    CHECKDB found 0 allocation errors and 8 consistency errors in database 'our_db'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (our_db).

    However, I performed the following index rebuild script on a copied test instance of the corrupted DB


    USE DatabaseName --Enter the name of the database you want to reindex 

    DECLARE @TableName varchar(255) 

    DECLARE TableCursor CURSOR FOR 

    SELECT table_name FROM information_schema.tables 

    WHERE table_type = 'base table' 

    OPEN TableCursor 

    FETCH NEXT FROM TableCursor INTO @TableName 

    WHILE @@FETCH_STATUS = 0 

    BEGIN 

    DBCC DBREINDEX(@TableName,' ',90) 

    FETCH NEXT FROM TableCursor INTO @TableName 

    END 

    CLOSE TableCursor 

    DEALLOCATE TableCursor

    The only issue is I believe using the DBREINDEX will take the database tables offline or lock them until complete, which isn't ideal as it is a heavily used production system so hard to find downtime - is there a similar script that can keep things online whilst the work is carried out?

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    You only need to rebuild index ID on table ID 50099219 - surely the downtime for that is a small price to pay for fixing the corruption?  You could schedule it for a quiet (or at least a less busy) time.  If you have Enterprise Edition, you can do an online index rebuild.

    By they way, I would recommend that you use the modern ALTER INDEX REBUILD syntax instead of the deprecated DBCC DBREINDEX.  And don't apply a blanket fill factor of 90 - test what the index's current fill factor is and rebuild with that value - unless you have a good reason to change the fill factor for a particular index.

    John

  • This was removed by the editor as SPAM

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

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