…try SQL Backup pro for faster, smaller, more robust backups.

Problem
One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found.  One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range. 

Solution
SQL Server 2005 offers a new option to the DBCC CHECKDB and DBCC CHECKTABLE commands.  This new option is the "DATA_PURITY" check which will look for issues where column values are not valid or out-of-range.  To run the command you issue it just as you would a regular DBCC command along with the added option, such as:

DBCC CHECKDB with DATA_PURITY

For databases that are created in SQL Server 2005, these checks are on by default, so using or not using the DATA_PURITY option does not affect the outcome of the checks when issuing a  DBCC CHECKDB or DBCC CHECKTABLE.

For databases that are not created in SQL Server 2005 and then later brought into SQL Server 2005 these checks are useful and will allow you to see if there are any data issues within the database.  Once this is run on a databases one of two results will occur; the first it will come back clean without problems and the second there will be data issues that need to be resolved.  If the purity check comes back without any problems a entry is made in the database header and whenever a DBCC CHECKDB or DBCC CHECKTABLE is issued the DATA_PURITY checks will also be performed regardless of whether you specify the DATA_PURITY check or not.

Here is sample output taken from Microsoft's support site to show what a purity check error might look like.

DBCC results for "account_history".
Msg 2570, Level 16, State 2, Line 1
Page (1:1073), slot 33 in object ID 1977058079, index ID 0, partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data"). Column "account_name_japan" value is out of range for data type "nvarchar". Update column to a legal value.
Msg 2570, Level 16, State 2, Line 1
Page (1:1156), slot 120 in object ID 1977058079, index ID 0, partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data"). Column "account_name_japan" value is out of range for data type "nvarchar". Update column to a legal value.
There are 153137 rows in 1080 pages for object "account_history".
CHECKDB found 0 allocation errors and 338 consistency errors in table "account_history" (object ID 1977058079).
CHECKDB found 0 allocation errors and 338 consistency errors in database 'BadUnicodeData'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So as you can see for any database that is migrated to SQL Server 2005 from a previous version this option should be run at least once until all of the data issues have been resolved. One this process runs clean all subsequent runs will automatically check for data purity issues.

Next Steps

• To learn more about the DATA_PURITY option take a look at DBCC CHECKDB or DBCC CHECKTABLE
• Troubleshooting DBCC error 2570 in SQL Server 2005
• Although Books Online does not show DATA_PURITY as an option for DBCC CHECKTABLE it is a valid option for the command
• Take a look at these other tips on maintenance tasks:
  • SQL Server Command Line Tools To Manage Your Server
  • SQL Server - Performing maintenance tasks
  • SQL Server 2000 to 2005 Crosswalk - Database Fragmentation
  • Maintenance Tips

• Ensure Data Purity Checks are Being Performed for your SQL Server databases
• Using DBCC PAGE to Examine SQL Server Table and Index Data
• Using DBCC UPDATEUSAGE to get accurate space allocation information in SQL Server
• SQL Server Database Integrity Checks Checklist
• DBCC SHOWCONTIG Blocking Issues in SQL Server
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback