May 19, 2009 at 2:53 pm
How to check this is enabled or not???
DBCC CHECKDB WITH DATA_PURITY
From 2005 onwords its enablled but the one whic is upgraded from older versions i need to confirm that this is enablled.
Is there any way to check this??
May 19, 2009 at 3:56 pm
Simply do
DBCC CHECKDB
It will perform all the checks you need.
The DATA_PURITY option just performs more limited checks.
.
May 19, 2009 at 4:46 pm
Tim Walker (5/19/2009)
The DATA_PURITY option just performs more limited checks.
DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default.
So in DBs created on SQL 2005/2008 DBCC CheckDB and DBCC CheckDB WITH DataPurity do the same thing. On a Db upgraded from 2000 or earlier, it's necessary to specify the option once the first time it's run.
I don't know if there's anywhere to check. Might be somewhere in the DB header, but I wouldn't know where to look.
Perhaps it's an idea to run it once with the Data_Purity option on each database, then you know (providing there are no errors) that it's enabled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 1:56 am
Thats a fair point, apologies for the incomplete information in my previous post.
Tim
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply