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

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):

DBCC CHECKDB (N'master') WITH DATA_PURITY, NO_INFOMSGS;
DBCC CHECKDB (N'model') WITH DATA_PURITY, NO_INFOMSGS;
GO

You can check the value for yourself using the following code:

DBCC TRACEON (3604);
DBCC DBINFO (N'master');
GO

Output from SQL Server 2012 SP1:

DBINFO STRUCTURE:

DBINFO @0x000000001E0BD400

dbi_version = 706                   dbi_createVersion = 706             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010008
dbi_crdate = 1900-01-01 00:00:00.000dbi_dbname = master                 dbi_dbid = 1
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 4000
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)                       dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 0:0:0 (0x00000000:00000000:0000)               dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 324:56:84 (0x00000144:00000038:0054)                    dbi_dbccFlags = 0
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)

24 thoughts on “Bug: DBCC CHECKDB data purity checks are skipped for master and model

  1. 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?

  2. 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…

  3. 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.

    Thanks Again!

  4. I’ve a client with a very large production database, dbi_version = 661, dbi_createVersion = 539, and dbi_dbccFlags = 0.

    Are there any anticipated downsides to setting dbi_dbccFlags = 2?

    Especially if there is a chance of non-conforming data?

    1. 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.

  5. maybe it’s worth mentioning this goes for all installs, so not only for upgraded sqlserver instances.

    It also goes for fresh installs of all versions SQLServer 2005 onward.

    Thank you for the notification.

  6. Paul,

    Just a note. The WITH TABLERESULTS parm works with DBCC DBINFO, so you can dump the results to a temp table with the following schema:

    CREATE TABLE #dbccinfo
    (ParentObject VARCHAR(256)
    ,Object VARCHAR(256)
    ,field SYSNAME
    ,value VARCHAR(256))

    I was able to run this script through CMS and check all instances:

    DECLARE @sqlstr VARCHAR(2000)

    CREATE TABLE #dbccinfo
    (ParentObject VARCHAR(256)
    ,Object VARCHAR(256)
    ,field SYSNAME
    ,value VARCHAR(256))

    SET @sqlstr = ‘DBCC DBINFO (N”master”) WITH TABLERESULTS’

    INSERT INTO #dbccinfo
    EXEC(@sqlstr)

    SELECT field, value FROM #dbccinfo
    WHERE field = ‘dbi_dbccFlags’

    DROP TABLE #dbccinfo

    And as always, thanks for the information.

  7. In checking on all my SQL Servers I have 1 SQL2008 Express instance and I found that the user database had the flaf set to 0 rather than 2. Is this expected?

    Chris

      1. Ok – so that’s 2000, which says there was no CHECKDB WITH DATA_PURITY run after the upgrade. I bet you’ll find the same create version for the other user databases with the flag set to 0.

  8. Looks like 539.

    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.

    Chris

    1. Ok – so that’s 2000, which says there was no CHECKDB WITH DATA_PURITY run after the upgrade. I bet you’ll find the same create version for the other user databases with the flag set to 0.

  9. Paul,

    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.

    Chris

  10. Now I understand this a lot better.

    We have databases that started in 7.0 and/or 2000 that have been upgraded. Data_purity is a 2005 check so those databases have existed without this check.

    Maybe you should talk to Bob again and have MS add this to their upgrade instructions.

    Chris

      1. I found the reference in 2008R2 BOL. Something we had missed here with previous 2000 to 2005 and onwards migrations.

  11. I had a question about the dbi_version if it is higher than 611 but the dbi_dbccFlags are set to 0. Does that mean Data purity Checks are on?

    Example I have a database with dbi_version =706 and dbi_dbccFlags 0.

    Thanks for your help

  12. OK, dbi_version >= 611 the dbi_dbccFlags = 2. Can these changes be implemented during use or it is better for after hours? and I’m guessing we should turn the TRACEOFF when we are done?

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.