April 12, 2006 at 12:49 am
hi,
We've encountered a torn page error and I've been searching for the object that's been corrupted (if any). Without any luck so far, yesterday I was able to retrieve some info with the DBCC PAGE and DBCC CHECKALLOC statements. I've posted the results below, it includes the page mentionned in the error and the previous and next ones. For the corrupted page I could not retrieve any information from the DBCC CHECKALLOC statement.
Tnx a million for any help I could get !
PAGE: (1:424415)
BUFFER:
BUF @0x03175D34
bpage = 0x2A35E000 bhash = 0x00000000 bpageno = (1:424415)
bdbid = 7 breferences = 0 bUse1 = 26483
bstat = 0xc00109 blog = 0x999a2159 bnext = 0x00000000
PAGE HEADER:
Page @0x2A35E000
m_pageId = (1:424415) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x20
m_objId (AllocUnitId.idObj) = 317 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594058702848
Metadata: PartitionId = 72057594053853184 Metadata: IndexId = 1
Metadata: ObjectId = 2069582411 m_prevPage = (1:424414) m_nextPage = (1:424424)
pminlen = 25 m_slotCnt = 171 m_freeCnt = 1598
m_freeData = 6252 m_reservedCnt = 0 m_lsn = (356938:7738:357)
m_xactReserved = 0 m_xdesId = (0:227356398) m_ghostRecCnt = 0
m_tornBits = 570687489
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:420576) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
***************************************************************
Table T_ED_FILE_STATUS Object ID 2069582411.
Index ID 1, partition ID 72057594053853184, alloc unit ID 72057594058702848 (type In-row data). FirstIAM (1:77685). Root (1:401914). Dpages 38.
Index ID 1, partition ID 72057594053853184, alloc unit ID 72057594058702848 (type In-row data). 2369 pages used in 297 dedicated extents.
Index ID 2, partition ID 72057594053984256, alloc unit ID 72057594058833920 (type In-row data). FirstIAM (1:84975). Root (1:432722). Dpages 1183.
Index ID 2, partition ID 72057594053984256, alloc unit ID 72057594058833920 (type In-row data). 1188 pages used in 149 dedicated extents.
Index ID 8, partition ID 72057594053787648, alloc unit ID 72057594058637312 (type In-row data). FirstIAM (1:29124). Root (1:391034). Dpages 947.
Index ID 8, partition ID 72057594053787648, alloc unit ID 72057594058637312 (type In-row data). 952 pages used in 120 dedicated extents.
Index ID 9, partition ID 72057594053918720, alloc unit ID 72057594058768384 (type In-row data). FirstIAM (1:84817). Root (1:427138). Dpages 947.
Index ID 9, partition ID 72057594053918720, alloc unit ID 72057594058768384 (type In-row data). 952 pages used in 120 dedicated extents.
Total number of extents is 686.
***************************************************************
PAGE: (1:7919)
BUFFER:
BUF @0x03175614
bpage = 0x2A32E000 bhash = 0x00000000 bpageno = (1:424416)
bdbid = 7 breferences = 0 bUse1 = 26483
bstat = 0xc00909 blog = 0x159a2159 bnext = 0x00000000
PAGE HEADER:
Page @0x2A32E000
m_pageId = (1:7919) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8008
m_objId (AllocUnitId.idObj) = 2 m_indexId (AllocUnitId.idInd) = 255
Metadata: AllocUnitId = 71776119061348352 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 7
m_freeCnt = 1814 m_freeData = 8088 m_reservedCnt = 748
m_lsn = (355742:1918:39) m_xactReserved = 748 m_xdesId = (0:225596300)
m_ghostRecCnt = 0 m_tornBits = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:420576) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
***************************************************************
***************************************************************
PAGE: (1:424417)
BUFFER:
BUF @0x03222DDC
bpage = 0x2EC3A000 bhash = 0x00000000 bpageno = (1:424417)
bdbid = 7 breferences = 0 bUse1 = 26483
bstat = 0xc00109 blog = 0x159a2159 bnext = 0x00000000
PAGE HEADER:
Page @0x2EC3A000
m_pageId = (1:424417) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 1045070959 m_indexId (AllocUnitId.idInd) = 1
Metadata: AllocUnitId = 349964747079680 Metadata: PartitionId = 349964747079680
Metadata: IndexId = 1 Metadata: ObjectId = 1045070959 m_prevPage = (1:424416)
m_nextPage = (1:424418) pminlen = 21 m_slotCnt = 311
m_freeCnt = 10 m_freeData = 7560 m_reservedCnt = 0
m_lsn = (356457:6635:41) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:420576) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
***************************************************************
Table T_KPB_HISTORY Object ID 1045070959.
Index ID 1, partition ID 349964747079680, alloc unit ID 349964747079680 (type In-row data). FirstIAM (1:723). Root (1:553000). Dpages 0.
Index ID 1, partition ID 349964747079680, alloc unit ID 349964747079680 (type In-row data). 6306 pages used in 787 dedicated extents.
Total number of extents is 787.
***************************************************************
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
April 13, 2006 at 3:15 am
Hi, probably not much use to you, but I believe once you get Torn Page Detected, you have no option but to go back to a reliable database backup. You could log a call with MS and see if there is a way to repair it. I had this issue in the past and bottom line restore. It's also a question in the MS 72-2228 exam. Rgds Derek.
April 13, 2006 at 8:08 am
if the torn page occured on an index, you can rebuild the index to eliminate the problem.
April 13, 2006 at 8:14 am
Paul,
The problem is that from the info I obtained with the DBCC PAGE statement, it is impossible for me to know to which object the page is referring.
In other words, I don't know if its data, text or an index.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
April 13, 2006 at 8:21 am
Derek,
The problem is that the DBA responsible for that database had ignored the error for a while since it only occured (not daily, but frequently) at night during the execution of a job. Recently the error has popped up during business hours which didn't go unnoticed of course.
This means that we now should restore a database from more then two months old and the hourly backups from the transactionlog from that point on... Quite an endeavor if you ask me.
That being said, it seems that there is an antivirus scanner (McAfee) installed on the server which didn't exclude the *.mdf, *.ldf and *.ndf files. This has now been changed and this morning we have not received the error. It is of course too early to crack open the champagne bottles, since the error doesn't show up each day, so it is still possible it returns tomorrow or some other day.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply