Ensure SQL Server Data Purity Checks are Performed

By:   |   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

 you can see two SQL Server instances; a 2008 instance and a 2000 instance

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ken Simmons 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


Article Last Updated: 2010-04-20

Comments For This Article




Wednesday, May 23, 2012 - 10:36:25 AM - Vikram Back To Top (17607)

 

I got it it was basically case sensitive issue. Thank you


Wednesday, May 23, 2012 - 10:20:13 AM - Vikram Back To Top (17606)

I ran the same query and getting the below error. I am not able to see the stored procedure on master db. i am on SQL SERVER 2008 R2

ERROR MESSAGE: Could not find stored procedure 'master.dbo.sp_Msforeachdb'.


Wednesday, April 21, 2010 - 2:34:26 AM - ALZDBA Back To Top (5271)

You're entirely correct.
I've mixed them up once more.

And indeed, they should both be in any upgrade scenario.


Tuesday, April 20, 2010 - 1:37:07 PM - SankarReddy Back To Top (5269)
I have to agree with Ken here. DATA_PURITY & PAGE_VERIFY options are 2 different things to solve entirely different problems. No relation at all between them.

Tuesday, April 20, 2010 - 9:58:31 AM - KenSimmons Back To Top (5268)

ALZDBA,

The Page Verify option is something that should be changed when upgrading also, but it is different than the Data Purity flag.  You can change the page verify option using ALTER DATABASE, and you set the Data Purity flag running DBCC with DATA_PURITY.

 

Ken


Tuesday, April 20, 2010 - 7:32:13 AM - ALZDBA Back To Top (5265)

 You could also use

select name, [page_verify_option_desc]
from master.sys.databases
where [page_verify_option_desc] != 'CHECKSUM'


 

Altough I have seen databases in this result that do have their dbi_DBCCFlags at 2 !

( some show  page_verify_option_desc None, others show page_verify_option_desc torn_page_detection )

(SQL2008 SP1 dev edtn 32bit)















get free sql tips
agree to terms