October 19, 2017 at 9:22 am
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?
October 19, 2017 at 9:37 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply