January 22, 2012 at 12:10 pm
Hi, we've recently encountered a corruption situation and I'm testing the latest backup to make sure it is okay. Based on the output, I think we can just restore table 'xxx' (by restoring the database on another server and they copying the table over) since most of the errors refer to it, but I'm not positive if that would fix the two consistency errors mentioned. Below is the DBCC output. Any help would be greatly appreciated!
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 (27:59906942) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
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 (27:59906943) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data): Page (27:59906941) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data), page (27:59906941). 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 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data). Page (27:59906941) was not seen in the scan although its parent (27:59907053) and previous (27:59906940) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 1
Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data): Page (27:59906942) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data). Index node page (27:59907053), slot 152 refers to child page (27:59906942) and previous child (27:59906941), but they were not encountered.
Msg 8928, Level 16, State 1, Line 1
Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data): Page (27:59906943) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data). Index node page (27:59907053), slot 153 refers to child page (27:59906943) and previous child (27:59906942), but they were not encountered.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 754101727, index ID 1, partition ID 72057594043170816, alloc unit ID 72057594047365120 (type In-row data). Page (27:59906944) is missing a reference from previous page (27:59906943). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'xxx' (object ID 754101727).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'yyy'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (yyy)
January 22, 2012 at 12:38 pm
There are 3 pages damaged (27:59906941, 27:59906942, 27:59906943)
Dropping the table should work as it will deallocate those pages. However if you have a clean backup and an unbroken chain of log backups, doing a page restore of those 4 pages should be a lot faster and, if you have Enterprise Edition, the restore will be an online operation.
Do you have the required backups?
Edit: corrected, 3 pages not 4.
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
January 22, 2012 at 12:43 pm
Thanks for the reply Gail. That's pretty much what I thought, but I really appreciate the confirmation. Unforunately, this is pretty much a batch server, so we keep it in simple recovery mode, but that might be changing really soon!
January 22, 2012 at 12:49 pm
Ah, well no page restores in simple recovery.
So the table can be completely recreated from another source? If so, then just run checkDB with repair and then sync up the missing data (3 pages of data will disappear in the repair). You can sync manually or get something like RedGate's SQLDataCompare to do it for you.
You also need to investigate the cause. It looks like a section on disk (24kb, which is a weird amount) has been zeroed out by something, likely some misbehaviour somewhere in the IO subsystem
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
January 22, 2012 at 1:00 pm
No page restores this time, but now I have a good reason to convince the powers that be to let me change it to full.
We have good backups (something that has been drummed into me via this forum!), so we'll be using that to restore the missing data. We think we have an idea of where the problem is and will work on resolving that tomorrow.
Thanks for all the help. It is much appreciated.
May 18, 2013 at 8:11 am
And the final outcome for this was?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply