Hi,We are getting index problems on 2 of our tables. The corruptions are intermittent and only started occurring on 22 Aug. We have been told by our customer that no changes have occurred on their systems recently, and I know that I haven't put anything new into the application either.Our customer is running (@@Version) Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Some Questions please.1. Is there anyway that the application could be causing the corruption.I have seen this from Paul Randal"Not possible ever for an app bug to cause database corruption - no matter how hard it tries.Paul RandalDev Lead, Microsoft SQL Server Storage Engine"Please forgive any unintended insult, but does Paul actually work for Microsoft on this product? I have seen some of his replies and he seems highly, highly conversant with the product and given my limited knowledge of SQL and DB in general I agree that the application should not be able to cause this problem. I need to be able to demonstrate this to both my boss and our customer and a statement like the above from Paul would go a long way to providing the proof I need.2. Why would the problem suddenly start on 22 Aug and happen regularly thereafter, given that there is supposedly no change to SQL or the application.We have not been able to replicate the problem in the test region, even though we are able to process the production files there. The customer is reluctant to start full SQL logging given the performance hit.We have a program which accepts a file of SQL statements and processes them. The input file can be up to about 50Mb and contain many thousands of statements. The structure of the file is this and the program just parses each statement between the <PGMBCL> and sends it to SQL<PGMBCL> DELETE FROM HRQUAARI.ZY8C WHERE NUDOSS = +714307848<PGMBCL> INSERT INTO HRQUAARI.ZY8C (NUDOSS,NULIGN,SOCDOS,FILLE1,PERTRT,FILLE2,NUMTRT,NUMBUL,NUMCUM,CUMANN,CUMPER,DATCUM,MONCUM,IDCY00) VALUES ( +714307848, +000001, 'OCM', '20', '0506', ' ', '0', '01', '178', '01', '10', '1753-01-01', +00000008072.2200, ' ')etc.. another 36 records ..etc<PGMBCL> DELETE FROM HRQUAARI.ZYTD12 WHERE NUDOSS = +714307848 AND CDINFO = '8C'<PGMBCL> INSERT INTO HRQUAARI.ZYTD12 (NUDOSS,CDINFO,TIMJIF,TEVERO,NOMBRE,NULMAX,NUCMAX) VALUES ( +714307848, '8C', '2005-09-23-14.36.05', '0', +00037, +000037, +000037)
and the DBCC CHECKTABLE ('HRUKPARI.ZY8c') results are :-Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:1462733:47) with values (NUDOSS = 712430859 and NULIGN = 48) points to the data row identified by (RID = (1:1453030:105)).Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X1ZY8C' (ID 112771509) (index ID 2). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:1468453:32) with values (NUDOSS = 712430858 and NULIGN = 50) points to the data row identified by (RID = (1:1453030:104)).Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:1064449:83) with values (NUDOSS = 712430858 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '02' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:104)).Server: Msg 8952, Level 16, State 1, Line 1Table error: Database 'HRUKPARI', index 'HRUKPARI.ZY8C.X2ZY8C' (ID 112771509) (index ID 3). Extra or invalid key for the keys:Server: Msg 8956, Level 16, State 1, Line 1Index row (1:1463770:32) with values (NUDOSS = 712430859 and FILLE1 = '20' and PERTRT = '0507' and FILLE2 = ' ' and NUMTRT = '0' and NUMBUL = '01' and NUMCUM = '958' and CUMANN = '04' and CUMPER = '01' and DATCUM = Jan 1 1753 12:00AM) points to the data row identified by (RID = (1:1453030:105)).DBCC results for 'HRUKPARI.ZY8C'.There are 352118 rows in 2656 pages for object 'HRUKPARI.ZY8C'.CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'HRUKPARI.ZY8C' (object ID 112771509).repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (HRUKPARI.HRUKPARI.ZY8C ).
Missing or Invalid Keys
Posted - 2005-09-27 : 07:09:28
It is impossible for the application to cause this kind of index corruption, because the application has no control over the maintainence of nonclustered indexes. The basic problem you are seeing is that the leaf level of the nonclustered indexes 'HRUKPARI.ZY8C.X1ZY8C' and 'HRUKPARI.ZY8C.X2ZY8C' contain rows that point to rows that do not exist. This can only be caused by two things: (1) nonclustered index maintenance bugs in SQL Server, or (2) hardware errors such as lost writes, stale reads, or corruptions in the values of the index keys.I can't seem to access the bug database right now, but I believe there are some parallel index build bugs that were fixed in SP3 or SP4 for SQL 2000. Do you rebuild indexes regularly? Did you start rebuilding them on 8/22? What's the 'max degree of parallelism' setting on the server?Please post the results of a full CHECKDB to ensure that you don't have other corruption in your database: DBCC CHECKDB ('HRUKPARI') WITH ALL_ERRORMSGS, NO_INFOMSGS. Have you run full hardware diagnostics on this server and verified that the event log is clear of hardware errors? Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)