Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with DBCC CHECKDB output


Help with DBCC CHECKDB output

Author
Message
Gary Hines
Gary Hines
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1676
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)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47281 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Gary Hines
Gary Hines
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1676
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!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47281 Visits: 44392
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Gary Hines
Gary Hines
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1676
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.
Dimbulbz
Dimbulbz
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 76
And the final outcome for this was?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search