Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with DBCC CHECKDB output Expand / Collapse
Author
Message
Posted Sunday, January 22, 2012 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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)
Post #1239942
Posted Sunday, January 22, 2012 12:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1239945
Posted Sunday, January 22, 2012 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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!
Post #1239948
Posted Sunday, January 22, 2012 12:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1239952
Posted Sunday, January 22, 2012 1:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:25 AM
Points: 13, Visits: 1,556
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.

Post #1239956
Posted Saturday, May 18, 2013 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:39 PM
Points: 19, Visits: 65
And the final outcome for this was?
Post #1454261
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse