Archive for May 21st, 2010

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 –

DBCC DBINFO (AdventureWorks) WITH TABLERESULTS

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.

Continue reading...