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)

A few seats still available in September classes in WA

There are only a few weeks to go until our September set of classes start in Bellevue, WA and there are still some open seats available.

  • IE1 Immersion Event on Internals and Performance, 9/16-20, is sold out at 36
  • IEHW Immersion Event on SQL Server Hardware, 9/18-19, has several seats left, plus a two-for-one deal – see HERE for details
  • IE2 Immersion Event on Performance Tuning, 9/23-27, has only four seats left – see HERE for details
  • IEo Immersion Event for the Accidental/Junior DBA, 9/30-10/2, has several seats left, plus a two-for-one deal – see HERE for details
  • IETS Immersion Event on Advanced T-SQL, 9/30-10/3, has several seats left – see HERE for details

We hope to see you at one of these events!

Survey: tempdb and transaction log IO latencies

Time for another survey!

This survey’s very simple: run the following code on as many of your production instances as you can, and post a comment with the results or send me plain text/spreadsheet output in an email. Please do not add any more info to the results (like server name, commas, lines) as it’s not relevant for this survey and adds a bunch of time to the results processing.

Results are good until 9/6/13 at least – keep ’em coming!

SELECT
	[database_id],
	[file_id],
    [ReadLatency] =
		CASE WHEN [num_of_reads] = 0
			THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
	[WriteLatency] =
		CASE WHEN [num_of_writes] = 0
			THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM
	sys.dm_io_virtual_file_stats (NULL, NULL)
WHERE
	[file_id] = 2 OR [database_id] = 2;
GO

 

database_id file_id ReadLatency          WriteLatency
----------- ------- -------------------- --------------------
1           2       0                    2
2           1       0                    6
2           2       0                    1
3           2       7                    9
4           2       10                   0
5           2       1                    1
7           2       6                    1
8           2       15                   55
9           2       13                   59
10          2       0                    0
11          2       44                   8


I’ll editorialize the results in a couple of weeks.

Thanks!