Ensure SQL Server Data Purity Checks are Performed

By: Ken Simmons   |   Updated: 2010-04-20   |   Comments (6)   |   Related: 1 | 2 | 3 | 4 | 5 | > Database Console Commands DBCCs

Problem

The DBCC CHECKDB command checks the integrity of the objects in a database and should be run on a regular basis. One thing that this command does not check in databases created in versions prior to SQL Server 2005 is the integrity of the data in the columns until it has been run once with the DATA_PURITY option. Is there a way to tell which databases on my SQL Server instance are not using this option?

Solution

Adding the DATA_PURITY option causes the CHECKDB command to look for column values that are invalid or out of range. Any database that was created in SQL Server 2005 or later will include the DATA_PURITY check by default; but if the database is being upgraded from an earlier version, you must run the command with the DATA_PURITY option at least once using the following command and then fix any data issues.

DBCC CHECKDB ([DatabaseName]) WITH DATA_PURITY 

Once the command has executed successfully and the issues have been resolved, an entry is made in the database header and the DATA_PURITY option will be included by default as a part of the normal CHECKDB operation. Therefore, in order to tell if your DBCC checks include the DATA_PURITY option, you have to look at the database header to see if the flag is enabled.

You can use the following script to do just that.

DBCC TRACEON (3604);;;
GO
CREATE TABLE #DBCC ( 
      ParentObject VARCHAR(255),
      [Object] VARCHAR(255),
      Field VARCHAR(255),
      [Value] VARCHAR(255)
) 
CREATE TABLE #DBCC2 ( 
     DatabaseName VARCHAR(255),
     ParentObject VARCHAR(255),
     [Object] VARCHAR(255),
     Field VARCHAR(255),
     [Value] VARCHAR(255)
)
EXEC master.dbo.sp_MSFOREACHDB 
'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'');
INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC;
DELETE FROM #DBCC'
SELECT * 
FROM #DBCC2
WHERE Field = 'dbi_DBCCFlags' AND 
      Value = 0 AND
      DatabaseName NOT IN ('master','model')
DROP TABLE #DBCC
DROP TABLE #DBCC2
GO

In the screenshot above, you can see two SQL Server instances; a 2008 instance and a 2000 instance.  I did a backup of the CMS database from the 2000 instance and restored it on the 2008 instance and as you can see, the only database without the DATA_PURITY option on my 2008 instance is the CMS database that I restored.

So if you have migrated databases from SQL 2000 to either 2005 or 2008 and you are not sure if you have turned on the DATA_PURITY option use this script to determine which databases need this option enabled.

Next Steps

• Review the following tip on how to resolve data purity issues.http://www.mssqltips.com/sqlservertip/1119/sql-server-2005-dbcc-checkdb-with-datapurity-command/
• Check out some other DBCC Tips http://www.mssqltips.com/category.asp?catid=76
• Review the DATA_PURITY option along with the remaining DBCC options in Books Online. http://msdn.microsoft.com/en-us/library/ms176064.aspx

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips