Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steez
Starting Member
5 Posts |
 Posted - 2005-08-04 : 06:14:54
|
| Hi all, weird output ... when running DBCC CHECKDB on a DB i get this error:Server: Msg 8966, Level 16, State 1, Line 1Could not read and latch page (4:3457816) with latch type SH. sysindexes failed.when running DBCC CHECKTABLE ('sysindexes') i get a little bit more info:Server: Msg 8966, Level 16, State 1, Line 1Could not read and latch page (4:3457816) with latch type SH. sysindexes failed.Server: Msg 8944, Level 16, State 1, Line 1Table error: Object ID 2, index ID 0, page (4:3457816), row 9. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 105 and 193.CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).I'm a bit clueless after seeing this and would like to know if someone has experienced this before and would be willing to offer some advice.Thank you! |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-04 : 10:44:43
|
| Hi steez,I'm afraid you need to restore from your backups.You have a corrupted page in the leaf level of sysindexes - this cannot be repaired. Pages in the leaf level of sysindexes, sysobjects, and syscolumns are critical to the operation of the database and cannot be regenerated from anywhere else - so CHECKDB has no choice but to bail out when it finds one of these corrupt.In this case, its found that the page is corrupt during the primitive system table sanity checks - which check the leaf level for valid, readable pages and that there are no loops in the page linkages.In your case, one of the rows has some bits set in the r_tagA field of the record that should not be set.Have you checked you SQL errorlog and Windows event log for evidence of h/w problems? Can you run memory diagnostics (as this could be memory bit-flips causing this)?What version are you on and I'll check for any known issues (but none spring to mind)?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
steez
Starting Member
5 Posts |
Posted - 2005-08-05 : 03:47:16
|
| Thank you Paul for explaining this.I'm running SQL Enterprise, 8.00.2040, SP4. This on a windows 2003 SQL Server.I've seen this error only since a week, I searched the logs to see if there were any hardware failures around the time of the first time the error popped up, but apart from some Storage Agent warnings there wasn't much useful in there. Could you tell me a bit more about the severity of this error? As it only seems to appear in the logs when I actually try and do a checkdb or a showcontig, so it does not 'seem' to trouble the normal workings of sql server to much.As far as resolutions go, is a restore of just the sysindexes table enough? Or do i need to do a full restore (from a healthy point in time) of backup + transaction logs?appreciate your advice! |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-05 : 11:28:56
|
quote:
Originally posted by steez
Thank you Paul for explaining this.I'm running SQL Enterprise, 8.00.2040, SP4. This on a windows 2003 SQL Server.I've seen this error only since a week, I searched the logs to see if there were any hardware failures around the time of the first time the error popped up, but apart from some Storage Agent warnings there wasn't much useful in there. Could you tell me a bit more about the severity of this error? As it only seems to appear in the logs when I actually try and do a checkdb or a showcontig, so it does not 'seem' to trouble the normal workings of sql server to much.As far as resolutions go, is a restore of just the sysindexes table enough? Or do i need to do a full restore (from a healthy point in time) of backup + transaction logs?appreciate your advice!
Actually I've thought about this some more and its possible that you may be able to get away with this.The error is pretty simple as I explained before - a field in the record header that can only have certain values has more bits sets than is allowed - that's why I asked you to run memory diagnostics in case the corruption happened because of dodgy memory.Can you do a 'select * from sysindexes' and see if it stops with an error? If not, you could use 'DBCC PAGE (dbname, 4, 3457816, 3)' on that page to work out which index entry is contained in the corrupt row. This may also fail, saying the record is corrupt, which means you'll need to crack the record manually from the output of 'DBCC PAGE (dbname, 4, 3457816, 1)' to see which index is affected (post the output here and I'll do it for you). If its a non-clustered index, you may be able to simply drop and recreate that index and the error will go away.As far as restoring goes, you cannot restore a single table from a backup so it'll need to be a full database restore. If you're up for it, and the database is large, I recommend entertaining the manual repair option described above. However, no guarantees that this will work - in which case, if you have a full backup anyway, you haven't lost anything by trying. Up to you.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
steez
Starting Member
5 Posts |
Posted - 2005-08-08 : 03:54:50
|
| Again, thank you Paul for helping out with this and the time you put in this.As the database is pretty big, restoring would be my last option. So hopefully I can do it by the 1st way you suggested. - A select * on sysindexes fails (when it hits the corrupt error)- DBCC PAGE (dbname, 4, 3457816, 1)+TRACEON(3604) gave me the following output:---< BEGIN >-----------------------------------------------------DBCC execution completed. If DBCC printed error messages, contact your system administrator.PAGE: (4:3457816)-----------------BUFFER:-------BUF @0x00FDF880---------------bpage = 0x22126000 bhash = 0x00000000 bpageno = (4:3457816)bdbid = 6 breferences = 1 bstat = 0x9bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x22126000----------------m_pageId = (4:3457816) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x2m_objId = 2 m_indexId = 0 m_prevPage = (6:660734)m_nextPage = (4:696807) pminlen = 82 m_slotCnt = 19m_freeCnt = 3154 m_freeData = 7864 m_reservedCnt = 0m_lsn = (17856:18995:3) m_xactReserved = 0 m_xdesId = (0:408024513)m_ghostRecCnt = 0 m_tornBits = 1025 Allocation Status-----------------GAM (4:3067392) = ALLOCATED SGAM (4:3067393) = NOT ALLOCATED PFS (4:3453576) = 0x40 ALLOCATED 0_PCT_FULL DIFF (4:3067398) = CHANGEDML (4:3067399) = NOT MIN_LOGGED DATA:-----Slot 0, Offset 0x60-------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 22126060: 00520030 25e688f4 00004812 00000000 0.R....%.H......22126070: 00010000 00000000 00040000 00010002 ................22126080: 00000000 00000000 00000000 00000000 ................22126090: 00000000 00000000 00280000 00000021 ..........(.!...221260A0: 00000000 00000000 00000000 00000000 ................221260B0: 001b0000 00000000 00a00003 80be00ae ................221260C0: 00a703a7 00000003 28010008 00000000 ...........(....221260D0: ffff0001 00000000 ffffffff 00000000 ................221260E0: 00a703a7 00000002 28010008 00000000 ...........(....221260F0: fffe0002 00000000 fffefffe 00000000 ................22126100: 00560054 004b0046 007e0044 00000030 T.V.F.K.D.~.0...22126110: 0000a8de 2be40000 00010000 0010 .......+......Slot 1, Offset 0x1dac---------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 22127DAC: 00520030 25e8e6ca 00004812 00000000 0.R....%.H......22127DBC: 00010000 00000000 00220000 00010004 ..........".....22127DCC: 00000000 00000000 00000000 00000000 ................22127DDC: 00000000 00000000 01520000 00000042 ..........R.B...22127DEC: 00000000 00000000 00000000 00000000 ................22127DFC: 001b0000 00000000 00e00003 810a00fa ................22127E0C: 00a703a7 0000000a 28010008 00000000 ...........(....22127E1C: ffff0001 00000000 ffffffff 00000000 ................22127E2C: 006a016a 0016000d 00000000 00000000 j.j.............22127E3C: 00010002 00000000 00010004 00000000 ................22127E4C: 006a016a 0016000d 00000000 00000000 j.j.............22127E5C: 000e0003 00000000 000e0011 00000000 ................22127E6C: 00380138 000a0004 00000000 00000000 8.8.............22127E7C: 001b0004 00000000 001b001e 00000000 ................22127E8C: 00560047 005f0044 004f004c 00460047 G.V.D._.L.O.G.F.22127E9C: 004c0049 007e0045 00000030 038dacd1 I.L.E.~.0.......22127EAC: 4da70000 00010003 0005 ...M......Slot 2, Offset 0x120--------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 22126120: 00520030 25e9b650 00004812 00000000 0.R.P..%.H......22126130: 00010000 00000000 00040000 00010003 ................22126140: 00000000 00000000 00000000 00000000 ................22126150: 00000000 00000000 00420000 0000002a ..........B.*...22126160: 00000000 00000000 00000000 00000000 ................22126170: 001b0000 00000000 00c00003 80e000d0 ................22126180: 00a703a7 00000003 28010008 00000000 ...........(....22126190: ffff0001 00000000 ffffffff 00000000 ................221261A0: 00a703a7 00000001 28010008 00000000 ...........(....221261B0: fffe0002 00000000 fffefffe 00000000 ................221261C0: 00a703a7 00000008 28010008 00000000 ...........(....221261D0: fffd0003 00000000 fffdfffd 00000000 ................221261E0: 00350054 00530043 00540058 0030007e T.5.C.S.X.T.~.0.221261F0: e8ac0000 00000026 002d82e7 00160005 ....&.....-.....Slot 3, Offset 0x200--------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 22126200: 00520030 25ea81a7 00004812 000ff9fa 0.R....%.H......22126210: 00010004 000ff9fc 00040004 00010002 ................22126220: 00000001 00000003 00000003 00000033 ............3...22126230: 00000000 00000000 003e0000 00000021 ..........>.!...22126240: 00000000 000ff9fb 00000004 00000000 ................22126250: 001b0000 00000000 00a00003 80c000b0 ................22126260: 00a703a7 00000002 28010008 00000000 ...........(....22126270: ffff0001 00000000 ffffffff 00000000 ................22126280: 00a703a7 00000003 28010008 00000000 ...........(....22126290: fffe0002 00000000 fffefffe 00000000 ................221262A0: 005a0054 00310041 00520031 0030007e T.Z.A.1.1.R.~.0.221262B0: ef480000 00000026 002ecca7 00180003 ..H.&...........Slot 4, Offset 0x2c0--------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 221262C0: 00520030 25eb4cfe 00004812 00000000 0.R..L.%.H......221262D0: 00010000 00000000 00040000 00010003 ................221262E0: 00000000 00000000 00000000 00000000 ................221262F0: 00000000 00000000 00d90000 00000035 ............5...22126300: 00000000 00000000 00000000 00000000 ................22126310: 001b0000 00000000 00c00003 80f000e0 ................22126320: 00a703a7 00000003 28010008 00000000 ...........(....22126330: ffff0001 00000000 ffffffff 00000000 ................22126340: 00a703a7 0000000a 28010008 00000000 ...........(....22126350: fffe0002 00000000 fffefffe 00000000 ................22126360: 00a703a7 0000000a 28010008 00000000 ...........(....22126370: fffd0003 00000000 fffdfffd 00000000 ................22126380: 004e0043 005f0056 00450044 00490052 C.N.V._.D.E.R.I.22126390: 00410056 00490054 004e004f 0030007e V.A.T.I.O.N.~.0.221263A0: f7540000 00000026 00297ea6 000c0001 ..T.&....~).....Slot 5, Offset 0x3b0--------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 221263B0: 00520030 25edaf03 00004812 000028c1 0.R....%.H...(..221263C0: 00010007 000028c3 00040007 00010002 .....(..........221263D0: 00000001 00000003 00000003 00000006 ................221263E0: 00000000 00000000 00240000 00000021 ..........$.!...221263F0: 00000000 000028c2 00000007 00000000 .....(..........22126400: 001b0000 00000000 00a00003 80be00ae ................22126410: 00a703a7 00000003 28010008 00000000 ...........(....22126420: ffff0001 00000000 ffffffff 00000000 ................22126430: 00a703a7 00000002 28010008 00000000 ...........(....22126440: fffe0002 00000000 fffefffe 00000000 ................22126450: 00350054 0047004b 007e0036 00000030 T.5.K.G.6.~.0...22126460: 00270ccd fc5c0000 0001002e 0013 ..'...\.......Slot 6, Offset 0x470--------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 22126470: 00520030 25ee7a5a 00004812 00002bfb 0.R.Zz.%.H...+..22126480: 00010007 00002c03 00040007 00010003 .....,..........22126490: 00000001 00000003 00000003 00000036 ............6...221264A0: 00000000 00000000 003c0000 00000024 ..........<.$...221264B0: 00000000 00002c02 00000007 00000000 .....,..........221264C0: 001b0000 00000000 00c00003 80de00ce ................221264D0: 00a703a7 00000003 28010008 00000000 ...........(....221264E0: ffff0001 00000000 ffffffff 00000000 ................221264F0: 00a703a7 00000001 28010008 00000000 ...........(....22126500: fffe0002 00000000 fffefffe 00000000 ................22126510: 00a703a7 00000002 28010008 00000000 ...........(....22126520: fffd0003 00000000 fffdfffd 00000000 ................22126530: 00370054 00370037 007e0050 00000030 T.7.7.7.P.~.0...22126540: 00296e41 77460000 00010034 0007 An)...Fw4.....Slot 7, Offset 0x16cc---------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 221276CC: 00520030 25ee7a5a 00800060 00000000 0.R.Zz.%`.......221276DC: 00020000 00000000 00010000 00010004 ................221276EC: 00000000 00000000 00000000 00000000 ................221276FC: 00000000 00000000 001e0000 00000024 ............$...2212770C: 00000000 00000000 00000000 00000000 ................2212771C: 001b0000 00000000 00e00003 811c010c ................2212772C: 00a703a7 00000001 28010008 00000000 ...........(....2212773C: ffff0002 00000000 fffffffe 00000000 ................2212774C: 00a703a7 00000003 28010008 00000000 ...........(....2212775C: fffe0001 00000000 ffffffff 00010000 ................2212776C: 00a703a7 00000001 28010008 00000000 ...........(....2212777C: ffff0002 00000000 fffefffe 00030000 ................2212778C: 00a703a7 00000002 28010008 00000000 ...........(....2212779C: fffd0003 00000000 fffdfffd 00010000 ................221277AC: 0057005f 005f0041 00790053 005f0073 _.W.A._.S.y.s._.221277BC: 0041004c 0047004e 005f0055 00350032 L.A.N.G.U._.2.5.221277CC: 00450045 00410037 00410035 f9440000 E.E.7.A.5.A...D.221277DC: 00000045 000a6fd9 00060003 E....o......Slot 8, Offset 0x18f4---------------------Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 221278F4: 00520030 25ee7a5a 00800060 00000000 0.R.Zz.%`.......22127904: 00030000 00000000 00010000 00010004 ................22127914: 00000000 00000000 00000000 00000000 ................22127924: 00000000 00000000 001e0000 00000024 ............$...22127934: 00000000 00000000 00000000 00000000 ................22127944: 001b0000 00000000 00e00003 811c010c ................22127954: 00a703a7 00000002 28010008 00000000 ...........(....22127964: ffff0003 00000000 fffffffd 00000000 ................22127974: 00a703a7 00000003 28010008 00000000 ...........(....22127984: fffe0001 00000000 ffffffff 00010000 ................22127994: 00a703a7 00000001 28010008 00000000 ...........(....221279A4: fffd0002 00000000 fffefffe 00010000 ................221279B4: 00a703a7 00000002 28010008 00000000 ...........(....221279C4: ffff0003 00000000 fffdfffd 00030000 ................221279D4: 0057005f 005f0041 00790053 005f0073 _.W.A._.S.y.s._.221279E4: 004c0050 00410056 005f0052 00350032 P.L.V.A.R._.2.5.221279F4: 00450045 00410037 00410035 f9450000 E.E.7.A.5.A...E.22127A04: 00000045 00357800 00110007 E....x5.....Slot 9, Offset 0x1010---------------------Row at offset 4112 may be corrupt. Hex dump of remaining 4042 bytes: 22127010: 00656d69 756f6300 7265746e 65707974 ime..countertype22127020: 65700000 6e696672 00786564 6c656800 ..perfindex..hel22127030: 646e6970 00007865 61666564 73746c75 pindex..defaults22127040: 656c6163 65700000 65646672 6c696174 cale..perfdetail22127050: 65500000 6e656372 6f725074 73736563 ..PercentProcess22127060: 6954726f 1500656d 09000000 00003c00 orTime.......<..22127070: 00000300 00006a00 00000a00 00080380 .....j..........22127080: 0cb50000 0cbd0000 0b010000 ff000000 ................22127090: 000ccaff 00080100 0cd70000 0ce90000 ................221270A0: 03010000 00000000 f6215105 0100000c .........Q!.....221270B0: 00000003 00000006 00000d01 00000301 ................221270C0: 00000700 000d0c00 00030100 00000000 ................221270D0: 0d1a0000 03010000 64000000 00000000 ...........d....221270E0: 746e6975 00003436 66726570 61666564 uint64..perfdefa221270F0: 00746c75 73694400 79616c70 656d614e ult..DisplayName22127100: 20250000 636f7250 6f737365 69542072 ..% Processor Ti22127110: 0000656d 6e756f63 74726574 00657079 me..countertype.22127120: 72657000 646e6966 00007865 706c6568 .perfindex..help22127130: 65646e69 64000078 75616665 6373746c index..defaultsc22127140: 00656c61 72657000 74656466 006c6961 ale..perfdetail.22127150: 72655000 746e6563 72657355 656d6954 .PercentUserTime22127160: 00001500 44000a00 03000000 5f000000 .......D......._22127170: 0a000000 03800000 00000008 00000da4 ................22127180: 00000dac 00000801 000db900 000dc600 ................22127190: 00030100 05000000 0dd32051 03010000 ........Q ......221271A0: 8e000000 de000000 0100000d 00000003 ................221271B0: 0000008f 00000de9 00000301 00000000 ................221271C0: 000df700 00030100 00c80000 75000000 ...............u221271D0: 36746e69 44000034 6c707369 614e7961 int64..DisplayNa221271E0: 0000656d 73552025 54207265 00656d69 me..% User Time.221271F0: 756f6300 7265746e 65707974 65700000 .countertype..pe22127200: 6e696672 00786564 6c656800 646e6970 rfindex..helpind22127210: 00007865 61666564 73746c75 656c6163 ex..defaultscale22127220: 65700000 65646672 6c696174 00000c00 ..perfdetail....22127230: 00000000 00000000 00010080 00000000 ................22127240: 00000000 00000000 00000000 00000000 ................22127250: 00000000 00290000 0000002f 00000000 ......)./.......22127260: 00000000 00000000 00000000 001b0000 ................22127270: 00000000 01000003 813c012c 00a703a7 ........,.<.....22127280: 00000002 28010008 00000000 ffff0002 .......(........22127290: 00000000 fffffffe 00000000 00a703a7 ................221272A0: 00000003 28010008 00000000 fffe0001 .......(........221272B0: 00000000 ffffffff 00010000 00a703a7 ................221272C0: 00000028 00690057 0033006e 005f0032 (...W.i.n.3.2._.221272D0: 00650050 00660072 00610052 00440077 P.e.r.f.R.a.w.D.221272E0: 00740061 00000061 00000048 00420034 a.t.a...H...4.B.221272F0: 00300030 00330044 00380036 00410035 0.0.D.3.6.8.5.A.22127300: 00310042 00420033 00340042 00350045 B.1.3.B.B.4.E.5.22127310: 00330042 00360037 00390045 00450032 B.3.7.6.E.9.2.E.22127320: 00310038 00410046 00310030 00550000 8.1.F.A.0.1...U.22127330: 00000028 00490057 0033004e 005f0032 (...W.I.N.3.2._.22127340: 00450050 00460052 00410052 00440057 P.E.R.F.R.A.W.D.22127350: 00540041 00000041 00520030 25f4a348 A.T.A...0.R.H..%22127360: 00800060 00000000 00030000 00000000 `...............22127370: 00010000 00010005 00000000 00000000 ................22127380: 00000000 00000000 00000000 00000000 ................22127390: 00290000 0000002f 00000000 00000000 ..)./...........221273A0: 00000000 00000000 001b0000 04000000 ................221273B0: 00fe0002 03a7012a 000600a7 00080000 ....*...........221273C0: 00002801 00030000 0000ffff fffd0000 .(..............221273D0: 0000ffff 03a70000 000300a7 00080000 ................221273E0: 00002801 00010000 0000fffe ffff0000 .(..............221273F0: 0000ffff 03a70001 000200a7 00080000 ................22127400: 00002801 00020000 0000fffd fffe0000 .(..............22127410: 0000fffe 03a70001 000600a7 00080000 ................22127420: 00002801 00030000 0000ffff fffd0000 .(..............22127430: 0000fffd 03a70003 000400a7 00080000 ................22127440: 00002801 00040000 0000fffc fffc0000 .(..............22127450: 0000fffc 005f0001 00410057 0053005f ......_.W.A._.S.22127460: 00730079 0052005f 00430053 004c0048 y.s._.R.S.C.H.L.22127470: 0032005f 00460035 00410034 00340033 _.2.5.F.4.A.3.4.22127480: 98290038 00520030 25f4a348 00800060 8.).0.R.H..%`...22127490: 00000000 00030000 00000000 00010000 ................221274A0: 00010005 00000000 00000000 00000000 ................221274B0: 00000000 00000000 00000000 00290000 ..............).221274C0: 0000002f 00000000 00000000 00000000 /...............221274D0: 00000000 001b0000 00000000 01000003 ................221274E0: 813c012c 00a703a7 00000006 28010008 ,.<............(221274F0: 00000000 ffff0003 00000000 fffffffd ................22127500: 00000000 00a703a7 00000003 28010008 ...............(22127510: 00000000 fffe0001 00000000 ffffffff ................22127520: 00010000 00a703a7 00000002 28010008 ...............(22127530: 00000000 fffd0002 00000000 fffefffe ................22127540: 00010000 00a703a7 00000006 28010008 ...............(22127550: 00000000 ffff0003 00000000 fffdfffd ................22127560: 00030000 00a703a7 00000004 28010008 ...............(22127570: 00000000 fffc0004 00000000 fffcfffc ................22127580: 00010000 0057005f 005f0041 00790053 ...._.W.A._.S.y.22127590: 005f0073 00530052 00480043 005f004c s._.R.S.C.H.L._.221275A0: 00350032 00340046 00330041 00380034 2.5.F.4.A.3.4.8.221275B0: 83db0000 00000045 000a6934 000d0006 ....E...4i......221275C0: 00520030 25ee7a5a 00800060 00000000 0.R.Zz.%`.......221275D0: 00020000 00000000 00010000 00010004 ................221275E0: 00000000 00000000 00000000 00000000 ................221275F0: 00000000 00000000 001e0000 00000024 ............$...22127600: 00000000 00000000 00000000 00000000 ................22127610: 001b0000 04000000 00de0002 03a7010a ................22127620: 000100a7 00080000 00002801 00020000 .........(......22127630: 0000ffff fffe0000 0000ffff 03a70000 ................22127640: 000300a7 00080000 00002801 00010000 .........(......22127650: 0000fffe ffff0000 0000ffff 03a70001 ................22127660: 000100a7 00080000 00002801 00020000 .........(......22127670: 0000ffff fffe0000 0000fffe 03a70003 ................22127680: 000200a7 00080000 00002801 00030000 .........(......22127690: 0000fffd fffd0000 0000fffd 005f0001 .............._.221276A0: 00410057 0053005f 00730079 004c005f W.A._.S.y.s._.L.221276B0: 004e0041 00550047 0032005f 00450035 A.N.G.U._.2.5.E.221276C0: 00370045 00350041 98290041 00520030 E.7.A.5.A.).0.R.221276D0: 25ee7a5a 00800060 00000000 00020000 Zz.%`...........221276E0: 00000000 00010000 00010004 00000000 ................221276F0: 00000000 00000000 00000000 00000000 ................22127700: 00000000 001e0000 00000024 00000000 ........$.......22127710: 00000000 00000000 00000000 001b0000 ................22127720: 00000000 00e00003 811c010c 00a703a7 ................22127730: 00000001 28010008 00000000 ffff0002 .......(........22127740: 00000000 fffffffe 00000000 00a703a7 ................22127750: 00000003 28010008 00000000 fffe0001 .......(........22127760: 00000000 ffffffff 00010000 00a703a7 ................22127770: 00000001 28010008 00000000 ffff0002 .......(........22127780: 00000000 fffefffe 00030000 00a703a7 ................22127790: 00000002 28010008 00000000 fffd0003 .......(........221277A0: 00000000 fffdfffd 00010000 0057005f ............_.W.221277B0: 005f0041 00790053 005f0073 0041004c A._.S.y.s._.L.A.221277C0: 0047004e 005f0055 00350032 00450045 N.G.U._.2.5.E.E.221277D0: 00410037 00410035 f9440000 00000045 7.A.5.A...D.E...221277E0: 000a6fd9 00060003 00520030 25ee7a5a .o......0.R.Zz.%221277F0: 00800060 00000000 00030000 00000000 `...............22127800: 00010000 00010004 00000000 00000000 ................22127810: 00000000 00000000 00000000 00000000 ................22127820: 001e0000 00000024 00000000 00000000 ....$...........22127830: 00000000 00000000 001b0000 04000000 ................22127840: 00de0002 03a7010a 000200a7 00080000 ................22127850: 00002801 00030000 0000ffff fffd0000 .(..............22127860: 0000ffff 03a70000 000300a7 00080000 ................22127870: 00002801 00010000 0000fffe ffff0000 .(..............22127880: 0000ffff 03a70001 000100a7 00080000 ................22127890: 00002801 00020000 0000fffd fffe0000 .(..............221278A0: 0000fffe 03a70001 000200a7 00080000 ................221278B0: 00002801 00030000 0000ffff fffd0000 .(..............221278C0: 0000fffd 005f0003 00410057 0053005f ......_.W.A._.S.221278D0: 00730079 0050005f 0056004c 00520041 y.s._.P.L.V.A.R.221278E0: 0032005f 00450035 00370045 00350041 _.2.5.E.E.7.A.5.221278F0: 00000041 00520030 25ee7a5a 00800060 A...0.R.Zz.%`...22127900: 00000000 00030000 00000000 00010000 ................22127910: 00010004 00000000 00000000 00000000 ................22127920: 00000000 00000000 00000000 001e0000 ................22127930: 00000024 00000000 00000000 00000000 $...............22127940: 00000000 001b0000 00000000 00e00003 ................22127950: 811c010c 00a703a7 00000002 28010008 ...............(22127960: 00000000 ffff0003 00000000 fffffffd ................22127970: 00000000 00a703a7 00000003 28010008 ...............(22127980: 00000000 fffe0001 00000000 ffffffff ................22127990: 00010000 00a703a7 00000001 28010008 ...............(221279A0: 00000000 fffd0002 00000000 fffefffe ................221279B0: 00010000 00a703a7 00000002 28010008 ...............(221279C0: 00000000 ffff0003 00000000 fffdfffd ................221279D0: 00030000 0057005f 005f0041 00790053 ...._.W.A._.S.y.221279E0: 005f0073 004c0050 00410056 005f0052 s._.P.L.V.A.R._.221279F0: 00350032 00450045 00410037 00410035 2.5.E.E.7.A.5.A.22127A00: f9450000 00000045 00357800 00110007 ..E.E....x5.....22127A10: 00520030 25f0dc5f 00800060 00000000 0.R._..%`.......22127A20: 00020000 00000000 00010000 00010004 ................22127A30: 00000000 00000000 00000000 00000000 ................22127A40: 00000000 00000000 00200000 00000026 .......... .&...22127A50: 00000000 00000000 00000000 00000000 ................22127A60: 001b0000 04000000 00de0002 03a7010e ................22127A70: 000100a7 00080000 00002801 00020000 .........(......22127A80: 0000ffff fffe0000 0000ffff 03a70000 ................22127A90: 000300a7 00080000 00002801 00010000 .........(......22127AA0: 0000fffe ffff0000 0000ffff 03a70001 ................22127AB0: 000100a7 00080000 00002801 00020000 .........(......22127AC0: 0000ffff fffe0000 0000fffe 03a70003 ................22127AD0: 000400a7 00080000 00002801 00030000 .........(......22127AE0: 0000fffd fffd0000 0000fffd 005f0001 .............._.22127AF0: 00410057 0053005f 00730079 0053005f W.A._.S.y.s._.S.22127B00: 00520050 00430041 00450048 0032005f P.R.A.C.H.E._.2.22127B10: 00460035 00440030 00350043 00000046 5.F.0.D.C.5.F...22127B20: 00520030 25f0dc5f 00800060 00000000 0.R._..%`.......22127B30: 00020000 00000000 00010000 00010004 ................22127B40: 00000000 00000000 00000000 00000000 ................22127B50: 00000000 00000000 00200000 00000026 .......... .&...22127B60: 00000000 00000000 00000000 00000000 ................22127B70: 001b0000 00000000 00e00003 81200110 .............. .22127B80: 00a703a7 00000001 28010008 00000000 ...........(....22127B90: ffff0002 00000000 fffffffe 00000000 ................22127BA0: 00a703a7 00000003 28010008 00000000 ...........(....22127BB0: fffe0001 00000000 ffffffff 00010000 ................22127BC0: 00a703a7 00000001 28010008 00000000 ...........(....22127BD0: ffff0002 00000000 fffefffe 00030000 ................22127BE0: 00a703a7 00000004 28010008 00000000 ...........(....22127BF0: fffd0003 00000000 fffdfffd 00010000 ................22127C00: 0057005f 005f0041 00790053 005f0073 _.W.A._.S.y.s._.22127C10: 00500053 00410052 00480043 005f0045 S.P.R.A.C.H.E._.22127C20: 00350032 00300046 00430044 00460035 2.5.F.0.D.C.5.F.22127C30: 57430000 00000046 000adb87 00090006 ..CWF...........22127C40: 0052003c 25e8e6ca 00000000 00000000 <.R....%........22127C50: 00000000 00000000 00220000 00010000 ..........".....22127C60: 00000000 00000000 00000000 00000000 ................22127C70: 00000000 00000000 01520000 00000000 ..........R.....22127C80: 00000000 00000000 ffff0000 00000000 ................22127C90: 001b0000 05000000 005e0002 00470074 ..........^.t.G.22127CA0: 00440056 004c005f 0047004f 00490046 V.D._.L.O.G.F.I.22127CB0: 0045004c 00520030 25e8e6ca 00004812 L.E.0.R....%.H..22127CC0: 00000000 00010000 00000000 00220000 ..............".22127CD0: 00010004 00000000 00000000 00000000 ................22127CE0: 00000000 00000000 00000000 01520000 ..............R.22127CF0: 00000042 00000000 00000000 00000000 B...............22127D00: 00000000 001b0000 04000000 00de0002 ................22127D10: 03a700f8 000a00a7 00080000 00002801 .............(..22127D20: 00010000 0000ffff ffff0000 0000ffff ................22127D30: 016a0000 000d006a 00000016 00000000 ..j.j...........22127D40: 00020000 00000001 00040000 00000001 ................22127D50: 016a0000 000d006a 00000016 00000000 ..j.j...........22127D60: 00030000 0000000e 00110000 0000000e ................22127D70: 01380000 00040038 0000000a 00000000 ..8.8...........22127D80: 00040000 0000001b 001e0000 0000001b ................22127D90: 00470000 00440056 004c005f 0047004f ..G.V.D._.L.O.G.22127DA0: 00490046 0045004c 0030007e 00520030 F.I.L.E.~.0.0.R.22127DB0: 25e8e6ca 00004812 00000000 00010000 ...%.H..........22127DC0: 00000000 00220000 00010004 00000000 ......".........22127DD0: 00000000 00000000 00000000 00000000 ................22127DE0: 00000000 01520000 00000042 00000000 ......R.B.......22127DF0: 00000000 00000000 00000000 001b0000 ................22127E00: 00000000 00e00003 810a00fa 00a703a7 ................22127E10: 0000000a 28010008 00000000 ffff0001 .......(........22127E20: 00000000 ffffffff 00000000 006a016a ............j.j.22127E30: 0016000d 00000000 00000000 00010002 ................22127E40: 00000000 00010004 00000000 006a016a ............j.j.22127E50: 0016000d 00000000 00000000 000e0003 ................22127E60: 00000000 000e0011 00000000 00380138 ............8.8.22127E70: 000a0004 00000000 00000000 001b0004 ................22127E80: 00000000 001b001e 00000000 00560047 ............G.V.22127E90: 005f0044 004f004c 00460047 004c0049 D._.L.O.G.F.I.L.22127EA0: 007e0045 00000030 038dacd1 4da70000 E.~.0..........M22127EB0: 00010003 00000005 00000000 00000000 ................22127EC0: 00000000 00000000 00000000 00000000 ................22127ED0: 00000000 00000000 00000000 00000000 ................22127EE0: 00000000 00000000 00000000 00000000 ................22127EF0: 00000000 00000000 00000000 00000000 ................22127F00: 00000000 00000000 00000000 00000000 ................22127F10: 00000000 00000000 00000000 00000000 ................22127F20: 00000000 00000000 00000000 00000000 ................22127F30: 00000000 00000000 00000000 00000000 ................22127F40: 00000000 00000000 00000000 00000000 ................22127F50: 00000000 00000000 00000000 00000000 ................22127F60: 00000000 00000000 00000000 00000000 ................22127F70: 00000000 00000000 00000000 00000000 ................22127F80: 00000000 00000000 00000000 00000000 ................22127F90: 00000000 00000000 00000000 00000000 ................22127FA0: 00000000 00000000 00000000 00000000 ................22127FB0: 00000000 00000000 00000000 00000000 ................22127FC0: 00000000 00000000 00000000 00000000 ................22127FD0: 00000000 00000000 0d38 ........8.OFFSET TABLE:-------------Row - Offset 18 (0x12) - 3384 (0xd38) 17 (0x11) - 2692 (0xa84) 16 (0x10) - 2104 (0x838) 15 (0xf) - 5252 (0x1484) 14 (0xe) - 4636 (0x121c) 13 (0xd) - 1848 (0x738) 12 (0xc) - 1616 (0x650) 11 (0xb) - 1360 (0x550) 10 (0xa) - 6944 (0x1b20) 9 (0x9) - 4112 (0x1010) 8 (0x8) - 6388 (0x18f4) 7 (0x7) - 5836 (0x16cc) 6 (0x6) - 1136 (0x470) 5 (0x5) - 944 (0x3b0) 4 (0x4) - 704 (0x2c0) 3 (0x3) - 512 (0x200) 2 (0x2) - 288 (0x120) 1 (0x1) - 7596 (0x1dac) 0 (0x0) - 96 (0x60) DBCC execution completed. If DBCC printed error messages, contact your system administrator.---< END >-----------------------------------------------------There's mention of a corrupt row. Can you tell me more? Greets |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-08 : 13:44:37
|
| Can you also post the output using option 3 instead of option 1?Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-08-08 : 17:10:23
|
| From the output of DBCC PAGE, it looks like someone (or something) has walked all over this page. The contents of the page at address 22127010 contains data that has nothign to do with sysindexes entries -- it looks more like performance counter data. 22127010: 00656d69 756f6300 7265746e 65707974 ime..countertype22127020: 65700000 6e696672 00786564 6c656800 ..perfindex..hel22127030: 646e6970 00007865 61666564 73746c75 pindex..defaults22127040: 656c6163 65700000 65646672 6c696174 cale..perfdetail22127050: 65500000 6e656372 6f725074 73736563 ..PercentProcess22127060: 6954726f 1500656d 09000000 00003c00 orTime.......<..22127070: 00000300 00006a00 00000a00 00080380 .....j..........22127080: 0cb50000 0cbd0000 0b010000 ff000000 ................We refer to this as a "scribbler", and it typically happens when a process running inside SQL Server's address space (like an XPROC or a COM object) gets a reference to a piece of memory that doesn't belong to it and writes some data to it. I would *strongly* recommend you contact PSS to get them to help you set up some diagnostics to catch this. Unfortuantely though, there's not a lot of hope in fixing this without a backup.Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-08-08 : 18:01:23
|
| Paul and I just took a look at this together, and there may be something that we can do. You can manually force your data out of these tables and into new ones by selecting all of the rows out of the tables that have sysindexes entries on this page. The old objects will still exist in your database, as will the corrupt sysindexes page. You probably won't be able to drop them, as the delete of the corresponding sysindexes rows will fail. That means, to recover completely, you'll have to BCP all of your data from this database and into a new one so that you don't hit the corruption on the sysindexes page again in normal DB operation.I've searched through the page, and it looks like the following object IDs are affected by this corruption:635865332636020426636073552636125607636177662636333827636385882636789576636385882636542047636020426You can force the scan of the base table using a WITH (INDEX=1) or WITH (INDEX=0) hint (depending on whether it's a clustered index (1) or heap (0)). Obviously, this might take longer than restoring a backup, and is clearly more error-prone than restore. However, it's an option.You should get PSS involved to make sure that this doesn't happen again, regardless of which option you choose.Let me know if you have questions/problems.Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
steez
Starting Member
5 Posts |
Posted - 2005-08-09 : 07:24:28
|
| Thank you Paul and Ryan for your advice."Can you also post the output using option 3 instead of option 1?"No I can't, the statement ends in error: Server: Msg 7987, Level 22, State 1, Line 1A possible database consistency problem has been detected on database 'P11'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'P11'."You can manually force your data out of these tables and into new ones by selecting all of the rows out of the tables that have sysindexes entries on this page."I must say I don't fully understand. I believe you're saying that without a restore from backup, I can not get rid of this corrupted page in any way, correct?And what would a forced scan do? Thank you again for time and effort.Much appreciated. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-09 : 09:22:48
|
quote:
Originally posted by steez
Thank you Paul and Ryan for your advice."Can you also post the output using option 3 instead of option 1?"No I can't, the statement ends in error: Server: Msg 7987, Level 22, State 1, Line 1A possible database consistency problem has been detected on database 'P11'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'P11'.
That's what I suspected would happen as I said above. SP4 has code in to work around the corrupt record and try the next one.quote:
"You can manually force your data out of these tables and into new ones by selecting all of the rows out of the tables that have sysindexes entries on this page."I must say I don't fully understand. I believe you're saying that without a restore from backup, I can not get rid of this corrupted page in any way, correct?
Right - at this point you should restore from your backup. Even using the tricks Ryan's describing (and I alluded to earlier) you won't be able to force SQL Server to deallocate the broken page on its own. I suspected you may be able to if it was just a few bytes that were corrupt, but the gross nature of the corruption, over-writing several complete rows, mean you can't.quote:
And what would a forced scan do?
Would avoid trying to use a non-clustered index and bumping into one of the corrupt rows on this sysindexes page. If a customer doesn't have a backup, this technique can be used to circumvent bad rows in sysindexes and still retrieve all the table data.quote:
Thank you again for time and effort.Much appreciated.
You're most welcome.Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
steez
Starting Member
5 Posts |
Posted - 2005-08-22 : 03:50:42
|
| Hi all, i thought i would post the resolution to our problem as this might be a help to others with similar problems.We needed to do some patching on the server against the recent virus outbreaks and after the reboot there was no more sign of corruption. Before this i had already restored a backup to a different server, also in the restored database there was no sign of the corruption (which might explain the problem was related to the memory of the production server?). Well a happy ending after all. Thanks for all your help in this. |
|
|
|
tkaub
Starting Member
3 Posts |
Posted - 2005-10-17 : 07:01:14
|
| Hi,i have the same above problem but with a diffrent twist...1- when i run "DBcc CheckDB ('DBName') with all_errormsgs"i will Get:Could not read and latch page (1:173) with latch type SH. sysindexes failed.2- then :select * from sysindexesGave me:I/O error (torn page) detected during read at offset 0x0000000015a000 in file 'C:\Data\Databases\Old_Data.MDF'.Connection Broken3- dbcc checktable ('sysindexes')Could not read and latch page (1:173) with latch type SH. sysindexes failed.Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 2, index ID 0, page (1:173). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).Forth of all, I don't have any old backup....and i can't copy these tables from this database to another one...SO please advise urgently.... |
|
|
|
|
|
|
|
|
DBCC CHECKDB fails