April 17, 2015 at 9:32 am
Hello,
I'm looking for help and guidance on how to extract the remaining "good" rows from a table that DBCC CHECKDB has reported consistency errors on. A subset of the errors reported for the problem table are listed below:
Msg 8928, Level 16, State 1, Line 1
Object ID 405576483, index ID 1, partition ID 72057594038976512, alloc unit ID 72057594045136896 (type LOB data): Page (1:714993) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 405576483, index ID 1, partition ID 72057594038976512, alloc unit ID 72057594045136896 (type LOB data), page (1:714993). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 405576483, index ID 1, partition ID 72057594038976512, alloc unit ID 72057594045136896 (type LOB data): Page (1:714994) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 405576483, index ID 1, partition ID 72057594038976512, alloc unit ID 72057594045136896 (type LOB data): Page (1:714995) could not be processed. See other errors for details.
............
Msg 8928, Level 16, State 1, Line 1
Object ID 405576483, index ID 1, partition ID 72057594038976512, alloc unit ID 72057594045136896 (type LOB data): Page (1:715079) could not be processed. See other errors for details.
There are 4122 rows in 125 pages for object "AllDocVersions".
So far I have used the error messages to check the validity of the pages on either ‘logical’ side of the damaged pages - by inspecting pages 1:714992 (the upper bound of the lower range) and 1:715080 (the lower bound of the upper range) with the DBCC PAGE command:
DBCC TRACEON (3604);
DBCC PAGE ('WSS_Content_PetrotechLab', 1, 714992, 3);
GO
PAGE: (1:714992)
BUFFER:
BUF @0x037EDAE4
bpage = 0x5C336000 bhash = 0x00000000 bpageno = (1:714992)
bdbid = 15 breferences = 3 bUse1 = 59747
bstat = 0xc00009 blog = 0x21212159 bnext = 0x00000000
PAGE HEADER:
Page @0x5C336000
m_pageId = (1:714992) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 110 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045136896
Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1
Metadata: ObjectId = 405576483 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 0 m_slotCnt = 1 m_freeCnt = 40
m_freeData = 8150 m_reservedCnt = 0 m_lsn = (2725:58911:35)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -211205454
Allocation Status
GAM (1:511232) = ALLOCATED SGAM (1:511233) = NOT ALLOCATED
PFS (1:711744) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED
Blob row at: Page (1:714992) Slot 0 Length: 8054 Type: 3 (DATA)
Blob Id:16615079936
6256C06E: 00100208 00015453 00ff0004 00000000 ....ST..........
etc.....................................
and
DBCC TRACEON (3604);
DBCC PAGE ('WSS_Content_PetrotechLab', 1, 715080, 3);
GO
PAGE: (1:715080)
BUFFER:
BUF @0x02F502D8
bpage = 0x19A34000 bhash = 0x00000000 bpageno = (1:715080)
bdbid = 15 breferences = 3 bUse1 = 62449
bstat = 0x1c00009 blog = 0x21212159 bnext = 0x00000000
PAGE HEADER:
Page @0x19A34000
m_pageId = (1:715080) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 110 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045136896
Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 1
Metadata: ObjectId = 405576483 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 0 m_slotCnt = 1 m_freeCnt = 40
m_freeData = 8150 m_reservedCnt = 0 m_lsn = (2712:176496:44)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 2032202641
Allocation Status
GAM (1:511232) = ALLOCATED SGAM (1:511233) = NOT ALLOCATED
PFS (1:711744) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED
Blob row at: Page (1:715080) Slot 0 Length: 8054 Type: 3 (DATA)
Blob Id:16467230720
63EBC06E: 0000ff00 00000000 08000f01 43001002 ...............C
etc...........................
But because the pages are of type BLOB I can't see any reference to the actual key values the data belongs to and therefore can't work out which rows are good and which are bad. I am new to this so may be missing the obvious but would most appreciate it if someone can tell me how to link pages of type BLOB back to their parent pages (rows) or fix the corrupt pages for zero data loss (even better :-)).
I understand that there will probably be data loss and that the preferred method of recovery would be to restore the table from a good backup. Unfortunately, no good backups exist and those that do all report the same consistency errors on this particular table when restored. So it looks like the problem has been around for quite a while and has only come to the forefront because the group responsible for its maintenance are trying to export the data into another system.
Thanks for reading,
Greg
April 17, 2015 at 10:06 am
To be blunt, you probably aren't going to be able to do that.
The linkage is downwards. The data pages link to the text pages (and there can be multiple), there aren't backward links. Unless you know SQL's page structure, allocation mechanism, pointer format, etc backwards and want to spend the next week or two in a hex editor (not joking), you may have to settle for using DBCC Page to view the data, copy it piecemeal out into some external tool and run repair (it will deallocate all the damaged pages)
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
April 20, 2015 at 3:22 am
That's a shame but if that's the way it is then oh well we'll just have to accept it and try rescue the rows we know we can access. Thanks for you help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply