Unfixable DBCC CHECKDB error (that is also a false positive and otherwise strange) - by xor88

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


14
0
Sign in
to vote
ID 795478 Comments
Status Closed Workarounds
Type Bug Repros 9
Opened 7/31/2013 3:17:46 AM
Access Restriction Public

Description

Please run DBCC CHECKDB WITH extended_logical_checks on the database that I will attach. I will attach a SQL script to build it up as well as a backup.

The following error is reported:

The spatial index, XML index or indexed view 'mv_Universities_Users_ID' (object ID 341576255) contains rows that were not produced by the view definition.  This does not necessarily represent an integrity issue with the data in this database. For more information about troubleshooting DBCC errors on indexed views, see SQL Server Books Online.

Here are the fixes that I tried:
- Recreating the view entirely
- Rebuilding all indexes (also those on the base tables)
- Running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS (it executes a rebuild but the error persists)

Strangely, deleting the index IX_Users_ImageObjectID removes the error message. Recreating the index restores the error message. Removing the filter on the index and making it non-unique hides the error again.

Deleting all rows from either of the base tables removes the error. Removing just some of the rows does not remove the error.

I extracted the SQL query that DBCC CHECKDB runs to validate the indexed view contents using SQL Profiler. Running the query manually does not produce output, meaning that the view is indeed correctly populated. The query plan shows that the "mysterious" index mentioned above does not come into play at all.

It is unclear to me what exactly the bug is, but it is clear from all the spurious behavior listed above that there is at least one bug here.

TL;DR: DBCC CHECKDB reports a data error although no error is present. Also, the error depends on the presence of a seemingly unrelated index.


Here the DBCC CHECKDB internal query for reference:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
	SELECT 1 
	FROM   [dbo].[mv_Universities_Users_ID] tInner
	WHERE 
	(
		(([tInner].[Universities_ID] = [tOuter].[Universities_ID]) OR ([tInner].[Universities_ID] IS NULL AND [tOuter].[Universities_ID] IS NULL))
			AND (([tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]) OR ([tInner].[Users_ImageObjectID] IS NULL AND [tOuter].[Users_ImageObjectID] IS NULL))
	)
)
UNION ALL
SELECT[Universities_ID], [Users_ImageObjectID], 1 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter
WHERE NOT EXISTS
( 
	SELECT 1 
	FROM   [dbo].[mv_Universities_Users_ID] tInner WITH (NOEXPAND) 
	WHERE 
	(
		(([tInner].[Universities_ID] = [tOuter].[Universities_ID]) OR ([tInner].[Universities_ID] IS NULL AND [tOuter].[Universities_ID] IS NULL))
			AND (([tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]) OR ([tInner].[Users_ImageObjectID] IS NULL AND [tOuter].[Users_ImageObjectID] IS NULL))
      )
)
OPTION (EXPAND VIEWS)
Sign in to post a comment.
Posted by xor88 on 12/24/2015 at 11:03 AM
What's the progress on fixing this CHECKDB bug? It still exists as of 12.0.4416.0.

This bug is still causing our CHECKDB checks to fail. This causes a lot of problems since normally failing CHECKDB is a big warning.

I find it a little unsettling that CHECKDB bugs are left unfixed. I now wonder what other CHECKDB bugs are left unfixed? CHECKDB must work!
Posted by RodV8VK on 12/4/2014 at 10:20 PM
Bump! Any progress on this? bug listed as 'wont fix' and closed? SQL creates crash dump files when doing database integrity checks - this sounds like something that should be fixed.
Posted by Leo.G.Miller on 10/13/2014 at 2:29 PM
We are seeing this bug in SQL 2012, it means we get false positives every time the DBCC runs unless we exclude the relevant tables (not the best plan). I there any word on when this may be fixed?
Posted by xor88 on 4/29/2014 at 8:21 AM
This CHECKDB bug is still in 2014 CU1.
Posted by Brian Callaghan on 9/10/2013 at 7:05 PM
I am seeing the same errors. Also getting crash dump files created each I run DBCC checkdb ('MAGIX') with EXTENDED_LOGICAL_CHECKS , TABLERESULTS.
                            
                                                                
Posted by Drazen [MSFT] on 8/6/2013 at 8:40 AM
Thank you for submitting this report. We'll investigate at the earliest possible time.

Regards, Drazen
Posted by xor88 on 7/31/2013 at 10:30 AM
This issue has been analyzed by Paul White: http://dba.stackexchange.com/a/47365/6819 It was reproduced by him.
Posted by xor88 on 7/31/2013 at 7:01 AM
I just added "plans.zip" which contains different executions plans for the DBCC CHECKDB query. With different row counts in Universities I can produce at least three different plans. Only with the loop joins plan the issue does not occur. With merge and hash joins the bug is reproducible.

My SQL Server instance is configured with 1GB of RAM and no relevant trace flags.

Here are some useful scripts:

--initially Users contains 300k rows
--deleting 100k from Users still repros, deleting 150k no longer repros
--adding 500k random rows to Users no longer repros
--adding 2k rows to Universities still repros (now a merge join), also with 10k (now a hash join)

--first execute a DML statement, then execute the "reset"


--reset
ALTER INDEX [PK_Universities] ON Universities REBUILD
ALTER INDEX [PK_Users] ON Users REBUILD
ALTER INDEX IX_Users_ImageObjectID ON Users REBUILD
CHECKPOINT
DBCC freeproccache
DBCC DROPCLEANBUFFERS
GO
DBCC checkdb(CheckdbRepro3) WITH no_infomsgs, ALL_ERRORMSGS, TABLERESULTS, extended_logical_checks, data_purity


--create rows in Users
INSERT INTO Users (ID, ImageObjectID)
SELECT TOP 500000 NEWID(), NULL
FROM master.dbo.spt_values sv1
CROSS JOIN master.dbo.spt_values sv2


--delete rows from Users that would not pass the join
DELETE TOP (50000)
FROM Users
WHERE NOT EXISTS (SELECT * FROM Universities WHERE AdminUserID = Users.ID)


--create rows in Universities
INSERT INTO Universities (ID, AdminUserID)
SELECT TOP 1000 ABS(BINARY_CHECKSUM(NEWID())) AS ID, ID AS AdminUserID
FROM Users
ORDER BY NEWID()


--delete rows from Universities to make a NLJ more attractive - now it doesn't repro
DELETE TOP (600)
FROM Universities