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.
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.