DBCC CHECKDB WITH DATA_PURITY

Question

DBCC CHECKDB WITH DATA_PURITY

gs1975

Ten Centuries

Points: 1057
More actions
March 27, 2015 at 3:37 am

#295488

Hi,
We are currently planning a side-by-side SQL Upgrade from 2008r2 to 2014.
One of the post-Upgrade checks I would like to do is to run DBCC CHECKDB WITH DATA_PURITY against each database.
My manager is not so sure if this is still relevant for 2014 though. I have read a couple of articles which state that this should still be run,
but I wanted to get the thoughts of others on the forum, before I reply back to him.
Thanks,
George

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Absolutely. After upgrades and regularly as part of your normal maintenance (With Data_Purity is the default, but it's a good idea to specify it on the post-upgrade one)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gs1975 Ten Centuries Points: 1057 More actions · Answer 2

Thanks Gail.

Your reply provides me with the best supporting evidence I can give to my manager.

Many thanks.

George

Perry Whittle SSC Guru Points: 233825 More actions · Answer 3

gs1975 (3/27/2015)
Hi,
We are currently planning a side-by-side SQL Upgrade from 2008r2 to 2014.
One of the post-Upgrade checks I would like to do is to run DBCC CHECKDB WITH DATA_PURITY against each database.
My manager is not so sure if this is still relevant for 2014 though. I have read a couple of articles which state that this should still be run,

Depends on what version of SQL server the database was created, Your managers opinion may actually be valid.

Books Online states the following

SQL Server Books Online
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. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.

Paul Randal also blogged about this previously and pretty much confirmed this is the case.

So, if the database was created on SQL Server 2005 or above, the column level checks are automatically carried out, no need to run data purity.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Perry Whittle (3/27/2015)
gs1975 (3/27/2015)
Hi,
We are currently planning a side-by-side SQL Upgrade from 2008r2 to 2014.
One of the post-Upgrade checks I would like to do is to run DBCC CHECKDB WITH DATA_PURITY against each database.
My manager is not so sure if this is still relevant for 2014 though. I have read a couple of articles which state that this should still be run,
Depends on what version of SQL server the database was created, Your managers opinion may actually be valid.

For loose definitions of 'valid'

It may not be required to explicitly state DATA_PURITY, because on later versions CheckDB will run by default WITH DATA_PURITY, even if not specified. It's not going to harm to explicitly add the option.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gs1975 Ten Centuries Points: 1057 More actions · Answer 5

Thanks Perry/Gail for the additional feedback.

Perry - I understand what you are saying. The databases that are being upgraded are all 2008r2, which means I should not need to run it.

Even so, I am going to explicitly add the option as Gail suggests.

Thanks to you both.

George

Perry Whittle SSC Guru Points: 233825 More actions · Answer 6

gs1975 (3/27/2015)
Even so, I am going to explicitly add the option as Gail suggests.
Thanks to you both.
George

You can check it first, using the following against the chosen database

DBCC DBINFO WITH TABLERESULTS

Check the field dbi_dbccFlags, a value of 2 means the data purity checks are enabled automatically.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 233825 More actions · Answer 7

This is easier

IF OBJECT_ID('tempdb..#dbccoptions') IS NOT NULL

BEGIN

DROP TABLE #dbccoptions

END

CREATE TABLE #dbccoptions

(

[parentobject] VARCHAR(32)

, [object]VARCHAR(32)

, [field]VARCHAR(32)

, [value]VARCHAR(64)

)

INSERT INTO #dbccoptions

EXEC('DBCC DBINFO WITH TABLERESULTS')

SELECT field, value

FROM #dbccoptions

WHERE field = 'dbi_dbccFlags'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

gs1975 Ten Centuries Points: 1057 More actions · Answer 8

Hi Perry,

I just wanted to say thanks for the information you sent me. I have incorporated the scripts into my plan.

Kind regards,

George

Perry Whittle SSC Guru Points: 233825 More actions · Answer 9

You're welcome 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉