In Recovery...

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.

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.

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.

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!