Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not?
As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the valid range of values for the column’s data type. For databases created on SQL 2005 onwards, these checks are always performed by DBCC CHECKDB and cannot be turned off. For databases created on earlier versions, it’s a little more complicated.
In SQL Server versions prior to 2005, it was possible to import invalid data values into a database. These invalid values could cause query execution problems, or possibly even wrong results. In 2005, when the import ‘holes’ were closed, the data purity checks were added to DBCC CHECKDB, but not by default for upgraded databases. Because the possibility existed of upgraded database containing invalid values, the decision was made not to enable the data purity checks by default as this could lead people to suspect that the upgrade had caused corruptions that weren’t there on 2000 or before.
So, if you have a database that was upgraded and you want to run the data purity checks, you need to use the WITH DATA_PURITY option for DBCC CHECKDB. This is all documented in Books Online and I put it into the Release Notes for SQL 2005 as well. Back to the question though – at what point do you NOT need to specify the option? Well, for an upgraded database, if the WITH DATA_PURITY option is used and no problems are found, a bit is irrevocably flipped in the boot page (see Search Engine Q&A #20: Boot pages, and boot page corruption) and from that point onwards the data purity checks will be performed on the database whenever DBCC CHECKDB runs.
The problem is though, how can you tell whether the bit has been flipped? You need to look at the boot page. The easiest way to do that is to use the DBCC DBINFO command (undocumented, but perfectly safe). It’s the equivalent of using DBCC PAGE (‘dbname’, 1, 9, 3) to look at the boot page contents, as I explained in the blog post referenced in the previous paragraph.
There are two things you need to look for: what version of SQL Server created the database, and it the ‘create version’ is 2000 or lower, whether the special ‘data purity’ flag is set or not.
For a database created on SQL Server 2005:
DBCC TRACEON (3604);
GO
DBCC DBINFO (‘master’);
GODBINFO STRUCTURE:
DBINFO @0x66C8EF64
dbi_dbid = 1 dbi_status = 65544 dbi_nextid = 1984726123
dbi_dbname = master dbi_maxDbTimestamp = 16000 dbi_version = 611
dbi_createVersion = 611 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 1900-01-01 00:00:00.000
dbi_filegeneration = 0
dbi_checkptLSNm_fSeqNo = 7612 m_blockOffset = 224 m_slotId = 1
dbi_RebuildLogs = 0 dbi_dbccFlags = 0
dbi_dbccLastKnownGood = 2009-05-12 16:07:15.647
dbi_dbbackupLSN<snip>
If the dbi_createVersion is 611 or higher, the database was created on SQL Server 2005+ and will always have data purity checks performed.
Note: in 2013 I discovered a bug that prevents automatic data purity checks in the master and model databases – see my blog post here.
For an upgraded database (this is one of my pre-corrupted databases, you can get it from Conference corruption demo scripts and example corrupt databases):
DBCC TRACEON (3604);
GO
DBCC DBINFO (‘DemoCorruptMetadata’);
GODBINFO STRUCTURE:
DBINFO @0x6855EF64
dbi_dbid = 7 dbi_status = 16 dbi_nextid = 2089058478
dbi_dbname = DemoCorruptMetadata dbi_maxDbTimestamp = 100 dbi_version = 611
dbi_createVersion = 539 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2009-06-17 15:14:49.490
dbi_filegeneration = 0
dbi_checkptLSNm_fSeqNo = 10 m_blockOffset = 303 m_slotId = 1
dbi_RebuildLogs = 0 dbi_dbccFlags = 0
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
dbi_dbbackupLSN<snip>
This database has a dbi_createVersion lower than 611, so we need to look at the dbi_dbccFlags field. A value of 0 means that the data purity checks are not enabled by default. A value of 2 means they are enabled by default. You can easily check this out for your own databases.
Have fun!
9 thoughts on “CHECKDB From Every Angle: How to tell if data purity checks will be run?”
Hi Paul
Great article, very useful.
Please could you explain what a dbi_dbccFlags value of 1 indicates?
Thanks
Chris
Great article as always!
It is unfortunate for me that there does not appear to be a way to tell from a user in the db_owner database role, but knowing how to tell from the sysadmin server role is better than not being able to tell at all.
A value of 1 means that DBCC CHECKDB hasn’t been cleanly run since the database was created or the transaction log rebuilt.
Any idea what a dbi_dbccFlags value of 3 would indicate?
Nope
If I “create” a database either by restoring a backup of a different database, or by setting a database offline and copying / moving its files for the new database – will it have these properties set as those of the previous database? I’m guessing yes, and I could try it myself of course, but asking lets you put me straight e.g. “it doesn’t work on Fridays. Who wants to deal with those errors anyway when it’s the weekend.” :-) Thank you!
Yes – it’s a database-level setting in the boot page of the database.
“possibly even wrong results. In 2005, when the import ‘holes’ were closed, the data purity checks were added to DBCC CHECKDB”,
plz regarding above we are getting data_purity errors in a database created in sql 2019 even when built-in holes are plugged down how its possible. How data is getting into tables at first place is there not safeguards to prevent it plz
Likely corruption in your database, less likely to be a bug.