Corruption reported...but index rebuild resolves it

• john.round

  Old Hand

  Points: 372

  More actions

  October 19, 2017 at 9:22 am

  #340891

  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: 148809

  More actions

  October 19, 2017 at 9:37 am

  #1964366

  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

• September 26, 2018 at 10:45 pm

  This was removed by the editor as SPAM