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.
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).
Index_Name_Ais 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 CHECKTABLEis 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.
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.