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)