By: Greg Robidoux | Comments | Related: 1 | 2 | 3 | 4 | 5 | > Database Console Commands DBCCs
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.
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:
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
