0

I'm getting lost about one thing. According to MS docs full checkdb with no option does column-value checks, so what's the gain of running data_purity? By 'gain' I mean how detailed checks are being done, I'm not considering performance now.

Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?

Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB:

  • DBCC CHECKDB('AdventureWorks2019') WITH PHYSICAL_ONLY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.

Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 39189.

  • DBCC CHECKDB('AdventureWorks2019') WITH ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.

Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.

  • DBCC CHECKDB('AdventureWorks2019') WITH DATA_PURITY, ESTIMATEONLY

DBCC results for 'AdventureWorks2019'.

Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.

0

Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?

Yes, that's right. You only need to enable the DATA_PURITY option running CHECKDB on a pre-2005 created database once, if the result is clean then subsequent CHECKDBs without the option will automatically include the check. And it's part of the standard CHECKDB process in 2005 and above-created databases.

Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB

That's because it's the same check process - the option wouldn't have any effect in a SQL2005+ created database because CHECKDB does that check as standard.

1
  • Thanks a lot for confirming. – MadSlavDBA Mar 27 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.