CHECKDB and DATA_PURITY May 21st, 2010

Vinod Kumar

DBCC CHECKDB command performs several operations in additional to checking the consistency of allocation structures and various objects inside a database. One such important interesting activity added is the DATA_PURITY checks. In SQL Server 2000, DBCC CHECKDB did not evaluate the values present in the columns to ensure that they are valid and conform to the rules specified for the data types for those columns. But there was an undocumented trace flag which could be used to force DBCC CHECKDB to perform some limited data purity checks. But I will refrain from getting into those shortcuts …

In SQL Server 2005, a documented option is available for the DBCC CHECKDB command that can be used to force the data purity checks. This is the WITH DATA_PURITY option. There are a couple of important points that you need to understand regarding these checks:

  • The data purity checks are performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2005
  • The data purity checks cannot be disabled for databases that were created in SQL Server 2005
  • The data purity checks are not performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2000 or earlier
  • If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
  • In order for DBCC to perform data purity checks on a database created in SQL Server 2000 or earlier, you will need to execute DBCC CHECKDB with the DATA_PURITY option.
  • If this DBCC CHECKDB was successful, then a bit is turned on in the boot page to indicate future runs of DBCC CHECKDB perform data purity checks automatically. If you are still running SQL 2000 (you must upgrade), but for academic purposes you might want to see the bit info using the DBINFO command. This typical undocumented command can be used like this –


Paul on his blog talks about this interesting information of DBCCFlags bit out there. 0 means it will not be run and 2 will mean it will be enabled by default. There are specific checks performed for columns with the data types – bit, real, float, datetime, smalldatetime, decimal, numeric, binary, varbinary, nchar,nvarchar. There is a very good possibility that data purity checks show problems on upgraded databases because these checks were not performed in earlier versions unless the trace flag was used to force these checks.

Tags: , , , , ,

This entry was posted on Friday, May 21st, 2010 at 16:01 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “CHECKDB and DATA_PURITY”

  1. vinodkumar says:

    Rich – CHECKDB is an resource intensive operation, while it has been enhanced by miles in SQL 2005 and 2008 versions compared to SQL Server 2000 days. CHECKDB will be the only operation that will read through ALL your pages and check for consistency errors of need be. Even a backup can error out in ht emiddle, while a CHECKDB will identify and then walk past to check other errors.

    It is surely advisable to run these during off-peak

  2. Rich says:


    Can DBCC CHECKDB WITH_PURITY be run while the db is being used or must we set it to single user and kick everyone off? Is it very resource intensive or only slightly? Could we just wait until there is a lull in the activity or can I run it while folks are active?

  3. emt says:

    Wow this is a great resource.. I’m enjoying it.. good article

  4. jackadams says:

    Hi, The DATA_PURITY clause of the DBCC CHECKDB statement is used to check the database for column values that are out-of-range or invalid. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with precision values that are invalid. This is useful only for databases that have been upgraded from earlier versions of SQL Server because databases created in SQL Server 2005 and later versions have column-value integrity checks enabled by default.

Leave a Reply