{"id":806,"date":"2009-08-19T11:04:00","date_gmt":"2009-08-19T11:04:00","guid":{"rendered":"\/blogs\/paul\/post\/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx"},"modified":"2013-09-04T08:14:41","modified_gmt":"2013-09-04T15:14:41","slug":"checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/","title":{"rendered":"CHECKDB From Every Angle: How to tell if data purity checks will be run?"},"content":{"rendered":"

Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not?<\/em><\/span><\/span><\/p>\n

As you may know, DBCC CHECKDB<\/span> in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the valid range of values for the column’s data type. For databases created on SQL 2005 onwards, these checks are always performed by DBCC CHECKDB<\/span> and cannot be turned off. For databases created on earlier versions, it’s a little more complicated.<\/span><\/p>\n

In SQL Server versions prior to 2005, it was possible to import invalid data values into a database. These invalid values could cause query execution problems, or possibly even wrong results. In 2005, when the import ‘holes’ were closed, the data purity checks were added to DBCC CHECKDB<\/span>, but not by default for upgraded databases. Because the possibility existed of upgraded database containing invalid values, the decision was made not to enable the data purity checks by default as this could lead people to suspect that the upgrade had caused corruptions that weren’t there on 2000 or before.<\/span><\/p>\n

So, if you have a database that was upgraded and you want to run the data purity checks, you need to use the WITH DATA_PURITY<\/span> option for DBCC CHECKDB<\/span>. This is all documented in Books Online and I put it into the Release Notes for SQL 2005 as well. Back to the question though – at what point do you NOT need to specify the option? Well, for an upgraded database, if the WITH DATA_PURITY<\/span> option is used and no problems are found, a bit is irrevocably flipped in the boot page (see <\/span>Search Engine Q&A #20: Boot pages, and boot page corruption<\/span><\/a>) and from that point onwards the data purity checks will be performed on the database whenever DBCC CHECKDB<\/span> runs.<\/span><\/p>\n

The problem is though, how can you tell whether the bit has been flipped? You need to look at the boot page. The easiest way to do that is to use the DBCC DBINFO<\/span> command (undocumented, but perfectly safe). It’s the equivalent of using DBCC PAGE (‘dbname’, 1, 9, 3)<\/span>\u00a0to look at the boot page contents, as I explained in the blog post referenced in the previous paragraph.<\/span><\/p>\n

There are two things you need to look for: what version of SQL Server created the database, and it the ‘create version’ is 2000 or lower, whether the special ‘data purity’ flag is set or not.<\/span><\/p>\n

For a database created on SQL Server 2005:<\/span><\/p>\n

DBCC TRACEON (3604);
\nGO
\nDBCC DBINFO (‘master’);
\nGO<\/span><\/p>\n

DBINFO STRUCTURE:<\/span><\/p>\n

DBINFO @0x66C8EF64<\/span><\/p>\n

dbi_dbid = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_status = 65544\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_nextid = 1984726123
\ndbi_dbname = master\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_maxDbTimestamp = 16000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_version = 611
\ndbi_createVersion = 611<\/span><\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_ESVersion = 0
\ndbi_nextseqnum = 1900-01-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_crdate = 1900-01-01 00:00:00.000
\ndbi_filegeneration = 0
\ndbi_checkptLSN<\/span><\/p>\n

m_fSeqNo = 7612\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_blockOffset = 224\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_slotId = 1
\ndbi_RebuildLogs = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_dbccFlags = 0
\ndbi_dbccLastKnownGood = 2009-05-12 16:07:15.647
\ndbi_dbbackupLSN<\/span><\/p>\n

<snip><\/span><\/p><\/blockquote>\n

If the dbi_createVersion<\/span> is 611 or higher, the database was created on SQL Server 2005+ and will always have data purity checks performed.<\/span><\/p>\n

Note: in 2013 I discovered a bug that prevents automatic data purity checks in the master and model databases – see my blog post here<\/a>.<\/p>\n

For an upgraded database (this is one of my pre-corrupted databases, you can get it from <\/span>Conference corruption demo scripts and example corrupt databases<\/span><\/a>):<\/span><\/p>\n

DBCC TRACEON (3604);
\nGO
\nDBCC DBINFO (‘DemoCorruptMetadata’);
\nGO<\/span><\/p>\n

DBINFO STRUCTURE:<\/span><\/p>\n

DBINFO @0x6855EF64<\/span><\/p>\n

dbi_dbid = 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_status = 16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_nextid = 2089058478
\ndbi_dbname = DemoCorruptMetadata\u00a0\u00a0\u00a0\u00a0 dbi_maxDbTimestamp = 100\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_version = 611
\ndbi_createVersion = 539<\/span><\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_ESVersion = 0
\ndbi_nextseqnum = 1900-01-01 00:00:00.000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_crdate = 2009-06-17 15:14:49.490
\ndbi_filegeneration = 0
\ndbi_checkptLSN<\/span><\/p>\n

m_fSeqNo = 10\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_blockOffset = 303\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_slotId = 1
\ndbi_RebuildLogs = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbi_dbccFlags = 0<\/span><\/strong>
\ndbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
\ndbi_dbbackupLSN<\/span><\/p>\n

<snip><\/span><\/p><\/blockquote>\n

This database has a dbi_createVersion<\/span> lower than 611, so we need to look at the dbi_dbccFlags<\/span> field. A value of 0 means that the data purity checks are not enabled by default. A value of 2 means they are enabled by default. You can easily check this out for your own databases.<\/span><\/p>\n

Have fun!<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,30,31,34,48,62,100],"tags":[],"class_list":["post-806","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-corruption","category-database-maintenance","category-dbcc","category-inside-the-storage-engine","category-on-disk-structures","category-undocumented-commands"],"yoast_head":"\nCHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-08-19T11:04:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-09-04T15:14:41+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/\",\"name\":\"CHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-08-19T11:04:00+00:00\",\"dateModified\":\"2013-09-04T15:14:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CHECKDB From Every Angle: How to tell if data purity checks will be run?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/","og_locale":"en_US","og_type":"article","og_title":"CHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal","og_description":"Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/","og_site_name":"Paul S. Randal","article_published_time":"2009-08-19T11:04:00+00:00","article_modified_time":"2013-09-04T15:14:41+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/","name":"CHECKDB From Every Angle: How to tell if data purity checks will be run? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-08-19T11:04:00+00:00","dateModified":"2013-09-04T15:14:41+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CHECKDB From Every Angle: How to tell if data purity checks will be run?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/806","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=806"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/806\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=806"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=806"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=806"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}