2

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" that appear with no time-pattern that makes the db integrity fail and the process crash with all the logic consequences: manual work, reporting delay, users complaints, etc.

The error:

Msg 8952, Level 16, State 1, Line 1
Table error: table 'Table_Name' (ID 99999999). Index row in index 'Index_Name_A' (ID 2) does not match any data row. Possible extra or invalid keys for:

Msg 8956, Level 16, State 1, Line 1
Index row (1:24482:16) with values (LastName = 'Smith' and CustomerID = 22) pointing to the data row identified by (CustomerID = 22).

Details:

  • Index_Name_A is a nonclustered index
  • The table hosting the index is a fact table where 4 million rows or above are deleted/inserted on daily
  • The code that load the table has already in place a DISABLE/REBUILD INDEX process right before/after the action. However the problem is still there

Things I already tried:

  • Validated SEVERAL times targeted values to load are exactly the same datatype as in the destiny column with the exclusion of NULL values.
  • DROP/CREATE indexes rule instead of DISABLE/REBUILD. There were not improvements.
  • This only happens over NONCLUSTERED INDEXES of this specific table. CLUSTERED INDEXES of the same table or other NONCLUSTERED INDEXES of other fact tables does not have issues.
  • Under the assumption it would be an error on the physical pages where the table was stored, a new table was created, data exported to it and original table deleted. That was not the solution either.
  • DBCC CHECKTABLE is part of the current corrective actions with big costs: it needs to be executed under single user access during business hours. This is definitely the only way I have found to solve the problem but I need to keep affecting the business.

Question:

The question is pretty simple. Is there any way I can fix this error in a preventive manner? I don't want to keep doing corrective tasks to solve this issue. I'm sure there is a reason causing the error I've not discovered yet to fix this once and for all.

Hopefully somebody here will shed some light on this. I will be really grateful.

Thanks in advance.

  • What service pack is sql server 2005 running on ? Should be atleast running SP4 IMHO. Sort of similiar bug is reported with the error message that you are getting. – Kin Shah Apr 17 '15 at 15:35
  • @Kin Microsoft SQL Server 2005 - 9.00.5294.00 (X64) (Build 7601: Service Pack 1). Why do you suggest SP4? – G21 Apr 17 '15 at 15:39
  • 1
    Yes .. Install SP4 definitely. It has a lot of fixes. Also, sql server 2005 is way old now .. plan to upgrade to 2012 or 2014 (recommended) soon. – Kin Shah Apr 17 '15 at 15:43
  • @Kin Thanks for your quick comments. I will download and install those packages as per your recommendation. Will post findings within a few days. Thank you! – G21 Apr 17 '15 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.