TL;DR: I've got an unfixable corruption in an indexed view. Here are the details:
Running
DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS
on one of my databases produces the following error:
Msg 8907, Level 16, State 1, Line 1 The spatial index, XML index or indexed view 'ViewName' (object ID 784109934) contains rows that were not produced by the view definition. This does not necessarily represent an integrity issue with the data in this database. (...)
CHECKDB found 0 allocation errors and 1 consistency errors in table 'ViewName'.
repair_rebuild is the minimum repair level (...).
I do understand that this message indicates that the materialized data of the indexed view 'ViewName' is not identical with what the underlying query produces. However, manually verifying the data does not turn up any discrepancies:
SELECT * FROM ViewName WITH (NOEXPAND)
EXCEPT
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
EXCEPT
SELECT * FROM ViewName WITH (NOEXPAND)
NOEXPAND
was used to force use of the (only) index on ViewName
. FORCESCAN
was used to prevent indexed view matching from happening. The execution plan confirms both measures to be working.
No rows are being returned here, meaning that the two tables are identical. (There are only integer and guid columns, collations do not come into play).
The error cannot be fixed by recreating the index on the view or by running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
. Repeating the fixes also did not help. Why does DBCC CHECKDB
report this error? How to get rid of it?
(Even if rebuilding fixed it my question would still stand - why is an error reported although my data checking queries run successfully?)
Update: The bug has been fixed in some releases. I can no longer reproduce it in SQL Server 2014 SP2 CU 5. The 2014 SP2 KB contains a fix without KB article: Creating non-clustered index causes DBCC CheckDB With Extended_Logical_Checks to raise corruption error
. The two connect bugs about this have been closed:
- https://connect.microsoft.com/SQLServer/feedback/details/847233/creating-non-clustered-index-causes-dbcc-checkdb-with-extended-logical-checks-to-raise-corruption-error
- https://connect.microsoft.com/SQLServer/feedback/details/795478/unfixable-dbcc-checkdb-error-that-is-also-a-false-positive-and-otherwise-strange