Bug: DBCC CHECKDB data purity checks are skipped for master and model

It has been a long time since I wrote a post. I had so so much work to do, so that I had no time for posting 🙁

Anyway I want to change that in the future. I don’t promise that I will write every single week but I will do my best.

So about the bug. Last week, my colleague reported me that our consistency checking job was failed. More interestingly it failed on the master. Even more interestingly he said, when he tried to run the check manually with

the check run without any error. The error come up only when he used DATA_PURITY option:

This is what attracted my attention. I checked BOL which sait: “For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.”

Our affected instance is a ‘native’ SQL Server 2008 and I double-checked that so as the result of the DBCC CHECKDB command. Ok, from that time it was obvious to me that something is wrong. Maybe the way DBCC CHECKDB is working or the description of DATA_PURITY option in BOL or something else. Since we were dealing with system table inconsistency in the master database I contacted Paul Randal who is the expert of this field (too). Paul confirmed that there is bug here. See more about this bug on his blog…

This entry was posted in bug, DBCC, SQL. Bookmark the permalink.