{"id":873,"date":"2009-05-05T08:30:00","date_gmt":"2009-05-05T08:30:00","guid":{"rendered":"\/blogs\/paul\/post\/Misconceptions-around-corruptions-can-they-disappear.aspx"},"modified":"2017-11-18T16:30:27","modified_gmt":"2017-11-19T00:30:27","slug":"misconceptions-around-corruptions-can-they-disappear","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/","title":{"rendered":"Misconceptions around corruptions: can they disappear?"},"content":{"rendered":"

This has come up a few times now, most recently in an email question this morning\u00a0– subsequent runs of DBCC CHECKDB<\/em> show varying numbers of corruptions, and sometimes no corruptions – what’s going on? Even more strange – a maintenance job runs a DBCC CHECKDB<\/em>, which shows errors, but then in the morning – no consistency errors. What?<\/p>\n

I answered this back in the April 2009 SQL Q&A column<\/a><\/em> in TechNet Magazine, but I want to get it here on the blog too in a bit more detail. The answer has to do with the way the database is consistency checked, and how corruptions are detected.<\/p>\n

In 2005 onward, you’re going to be using page checksums to help detect corruption. If you created the database on 2005 onward, page checksums are enabled by default and every allocated page will have one. If you upgraded a database from 2000 or before, then you need to manually enable page checksums with ALTER DATABASE<\/em>. The nothing happens. Until a page is read in, changed, and then written back out. So your upgraded database will have a mixture of nothing\/page checksums, or torn-page detection\/page checksums. Note: torn-page protected pages remain torn-page protected, even with page checksums enabled, until the next time they’re altered. Then they get a page checksum. See Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?<\/a><\/em>\u00a0for an explanation and examples.<\/p>\n

Once you’ve got page checksums enabled, who can you tell if there are corruptions in the database? Well, there are a number of ways corruptions will show up:<\/p>\n

    \n
  1. You run an operation that hits a page that has been corrupted, and the page checksum test fails<\/li>\n
  2. You run a BACKUP … WITH CHECKSUM<\/em> and it finds a page with a bad checksum<\/li>\n
  3. You run a DBCC CHECKDB<\/em> and it finds a page with a bad checksum<\/li>\n<\/ol>\n

    That’s all very well, but what if a page *doesn’t* have a page checksum on it (because it hasn’t been changed since page checksums were enabled)? None of #1 to #3 will fail because of a bad page checksum, as there isn’t a page checksum to check. #1 might fail, depending on how corrupt the page is, and it will likely fail with an obscure message that doesn’t immediately scream ‘corruption’. #2 won’t fail, as the only time BACKUP<\/em> examines what it’s backing up is when WITH CHECKSUM<\/em> is enabled and a page has a page checksum on it. #3 might find the corruption, depending on how the page is corrupt. If the corruption is in the middle of a large varchar field, for instance, probably not. Your best bet is to have page checksums enabled and regularly run DBCC CHECKDB<\/em>.<\/p>\n

    That’s how corruptions are detected. So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn’t allocated to anything, then the 8192\u00a0bytes of it are meaningless and can’t be interpreted. Don’t get confused between reserved and allocated – I explain that in the first misconceptions post here<\/a>. As long as a page is allocated, it will be consistency checked\u00a0by DBCC CHECKDB<\/em>, including testing the page checksum, if it exists. A corruption can seem to ‘disappear’ if a corrupt page is allocated at the time\u00a0a DBCC CHECKDB<\/em> runs, but is then deallocated by the time the next DBCC CHECKDB<\/em> runs. The first time it will be reported as corrupt, but the second time\u00a0it’s not allocated, so it isn’t consistency checked and won’t be reported as corrupt. The corruption looks like it’s mysteriously vanished. But it hasn’t – it’s just that the corrupt page is no longer allocated. There’s nothing stopping SQL Server deallocating a corrupt page – in fact, that’s what many of the DBCC CHECKDB<\/em> repairs do – deallocate what’s broken, and fix up all the links.<\/p>\n

    The maintenance job phenomenon can occur because of the order of operations in the job. If the DBCC CHECKDB<\/em> is first, and then there’s an index rebuild, and the index rebuild happens to rebuild an index that DBCC CHECKDB<\/em> had found a corruption in, then the *new* index will have a completely different set of database pages, and won’t contain the corrupt page. Bingo – disappearing corruption. A subsequent DBCC CHECKDB<\/em> might not find any corruption, because the previously corrupt pages are no longer allocated.<\/p>\n

    Bottom line – any time you get corruption error messages, 99.99% of the time it’s your I\/O subsystem that’s got problems, even if the corruptions ‘disappear’.<\/p>\n","protected":false},"excerpt":{"rendered":"

    This has come up a few times now, most recently in an email question this morning\u00a0– subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what’s going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,31,34,52,53,61,63],"tags":[],"class_list":["post-873","post","type-post","status-publish","format-standard","hentry","category-corruption","category-database-maintenance","category-dbcc","category-involuntary-dba","category-io-subsystems","category-misconceptions","category-page-checksums"],"yoast_head":"\nMisconceptions around corruptions: can they disappear? - 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\/misconceptions-around-corruptions-can-they-disappear\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Misconceptions around corruptions: can they disappear? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This has come up a few times now, most recently in an email question this morning\u00a0– subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what’s going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-05-05T08:30:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-11-19T00:30:27+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=\"4 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\/misconceptions-around-corruptions-can-they-disappear\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/\",\"name\":\"Misconceptions around corruptions: can they disappear? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-05-05T08:30:00+00:00\",\"dateModified\":\"2017-11-19T00:30:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Misconceptions around corruptions: can they disappear?\"}]},{\"@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":"Misconceptions around corruptions: can they disappear? - 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\/misconceptions-around-corruptions-can-they-disappear\/","og_locale":"en_US","og_type":"article","og_title":"Misconceptions around corruptions: can they disappear? - Paul S. Randal","og_description":"This has come up a few times now, most recently in an email question this morning\u00a0– subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what’s going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/","og_site_name":"Paul S. Randal","article_published_time":"2009-05-05T08:30:00+00:00","article_modified_time":"2017-11-19T00:30:27+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/","name":"Misconceptions around corruptions: can they disappear? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-05-05T08:30:00+00:00","dateModified":"2017-11-19T00:30:27+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-corruptions-can-they-disappear\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Misconceptions around corruptions: can they disappear?"}]},{"@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\/873","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=873"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/873\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}