Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

SQL Server 2005 DBCC CHECKDB with DATA_PURITY command

MSSQLTips author Greg Robidoux By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Consistency Checks 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.

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



Last Update: 11/21/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.