In Recovery...

After going back and forth with someone with a corrupt master database, I’ve just discovered a bug. And unfortunately it goes back to SQL Server 2005 so the responsibility is mine for not realizing the problem at the time (but at least it’s not a bug in how my DBCC CHECKDB code works, it’s a bug in how master and model are created).

DBCC CHECKDB performs data purity checks to validate a column’s value is within the legal bounds of the column’s data type. It is supposed to do this for all databases created on SQL Server 2005 onward. This is based on the dbi_dbccFlags field in the database boot page being set to 2.

In all versions, the dbi_dbccFlags field for master and model are set to zero, so no automatic data purity checks are done. I’ve notified the dev team about this and it’s been confirmed as a bug.

Edit 9/4/13: I’ve heard from multiple people who’ve fixed this and found corruption issues in master – excellent! (that they found them, not that they had them :-)

Note: If you’re using Ola Hallengren‘s fabulous free scripts to run your consistency checks, your master and model are set correctly as he always uses WITH DATA_PURITY.

I recommend that you run the following to set the value correctly on all production instances on all versions from SQL Server 2005 onward (this is a one-time operation):

Hey Paul, thanks for ferreting out the issue and posting. I am sure you would have mentioned it if there were, but given that we are talking about master here I would like to be explicitly sure: are there any downsides to running the code you posted on any SQL Server version 2005 and up? Any other side-effects other than setting the dbi_dbccFlags value?

Thanks for the heads up… On most of my SQL Servers (3 out of 5) they already had the dbi_dbccFlags = 2, but that is probably because with the last database migration I ran DBCC CHECKDB WITH DATA_PURITY on all DBs on the servers, system DBs included, because I found one user DB that had the dbi_dbccFlags = 0 during the migration. (I swear that it had been created in 2005 or newer, so I’m not sure why it had a value of zero.)

But in any case I hadn’t done the same thing on the other two servers, so I fixed the system DBs on them just now…

Thanks for the information Paul. I have not checked ALL of the instances, but we do run data_purity checks once a week and physical_only the remaining days. I assume based on your information that they would all have gotten set by now, but I will eventually get to all of them and verify.

If there’s non-conforming data then you need to know about it. The upgrade and release notes for SQL Server 2005 both recommended running DBCC CHECKDB WITH DATA_PURITY on all databases – so the client didn’t follow the Microsoft recommendation when they upgraded from SQL Server 2000.

I am also seeing user databases with the flag set to 0 if the database was created empty and then replaced by a backup from a previous version SQL2005 to SQL2012. It seems a little inconsistent for the 2005 to 2012 but they may not have had a dbcc checkdb with physical_only run which we normally do after a version upgrade.

Looks like we need to run an actual DBCC CHECKDB with DATA_PURITY not say with PHYSICAL_ONLY when we migrate up from in this case SQL2005 to SQL2012. The with PHYSICAL_ONLY did not set the flag to 2 only the with DATA_PURITY did.

If dbccFlags = 2 then you’re already good and there’s nothing to do. Yes, turn off the TF when you’re done (although it has no effect unless you’re using undoc’d DBCC commands). If you do have to run WITH DATA_PURITY, I can’t say when the best time to run in your environment is – whenever you usually run DBCC CHECKDB on databases.