Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Ensure SQL Server Data Purity Checks are Performed


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Consistency Checks DBCCs

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 

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


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

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

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

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

 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)


Learn more about SQL Server tools