{"id":4814,"date":"2017-10-17T12:23:52","date_gmt":"2017-10-17T19:23:52","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4814"},"modified":"2019-12-30T17:37:00","modified_gmt":"2019-12-31T01:37:00","slug":"sqlskills-sql101-how-can-corruptions-disappear","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/","title":{"rendered":"SQLskills SQL101: How can corruptions disappear?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>As\u00a0<a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sqlskills-sql101-stored-procedures\/\" target=\"_blank\" rel=\"noopener noreferrer\">Kimberly blogged about earlier this year<\/a>, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">Every so often I get an email\u00a0question from a confused DBA: <em>why is it that sometimes corruptions seem to disappear?<\/em><\/p>\n<p style=\"text-align: justify;\">The situation is commonly as follows:<\/p>\n<ul style=\"text-align: justify;\">\n<li>There is a regular SQL Agent job that runs <em>DBCC CHECKDB<\/em><\/li>\n<li>One morning the DBA finds that the job failed, reporting corruptions in one of the\u00a0databases<\/li>\n<li>The DBA runs <em>DBCC CHECKDB<\/em> on that database again, but this time there are no\u00a0reported corruptions<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">This can lead the DBA to mistrust <em>DBCC CHECKDB<\/em>. Remember the SQL Server 2000 days\u00a0where sometimes <em>DBCC CHECKDB<\/em> occasionally reported corruptions when there weren\u2019t any?\u00a0Those days are long gone now: if <em>DBCC CHECKDB<\/em> reports corruption, then at that time that it ran there was definitely corruption.<\/p>\n<p style=\"text-align: justify;\">Think about what <em>DBCC CHECKDB<\/em> is doing: it reads and processes all the allocated pages in\u00a0the database \u2013 all the pages that are part of tables and indexes at the time that <em>DBCC CHECKDB<\/em>\u00a0runs. It doesn\u2019t check all the pages in the data files; only those that are currently being used. The\u00a0pages that are not currently allocated to an object cannot be checked as there\u2019s no \u201cpage history\u201d\u00a0maintained. There\u2019s really no way for <em>DBCC CHECKDB<\/em> to tell if they have ever been used\u00a0before or not and since they\u2019re not currently allocated there\u2019s no valid page structure on them\u00a0and no past to verify.<\/p>\n<p style=\"text-align: justify;\">And if your database is still being accessed then the set of allocated pages can change after\u00a0<em>DBCC CHECKDB<\/em> runs. A simple example of this occurring is:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Nonclustered index X of table Y has some corrupt pages in, which the <em>DBCC\u00a0CHECKDB<\/em> (being run by a SQL Agent job) reports<\/li>\n<li style=\"text-align: justify;\">Another SQL Agent job runs and performs index maintenance where it rebuilds index X (the rebuild operation always builds a new index and then drops the old index)<\/li>\n<li style=\"text-align: justify;\">The DBA runs <em>DBCC CHECKDB<\/em> manually and there are no corruptions reported in the\u00a0new index structure<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Nonclustered index corruption is the best kind of corruption to have. The rebuild operation\u00a0rewrote the index to a new set of pages and deallocated the pages that had corruption. When\u00a0<em>DBCC CHECKDB<\/em> is run manually, those new pages are not corrupt and the old pages are not\u00a0checked, as they are no longer in use.<\/p>\n<p style=\"text-align: justify;\">These kind of \u2018disappearing\u2019 corruptions are a problem because it\u2019s almost impossible to\u00a0investigate them further. However, they could indicate a problem with your I\/O subsystem. If\u00a0you find that they\u2019re occurring repeatedly, consider briefly preventing the process that causes the\u00a0corrupt pages to be deallocated so you can investigate the corruption.<\/p>\n<p style=\"text-align: justify;\">Another cause of disappearing corruptions can be transient I\/O subsystem problems, where page\u00a0reads sometimes fail outright and then succeed after that.<\/p>\n<p style=\"text-align: justify;\">And yet one more cause could be that the database is mirrored or is part of an availability group and the page was fixed by automatic page repair before the second <em>DBCC CHECKDB<\/em> occurred. You can look in the <em>msdb.dbo.suspect_pages<\/em> table (more details <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/suspect-pages-transact-sql\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>) for an entry for the broken page(s) with\u00a0<em>event_type<\/em> of 4.<\/p>\n<p style=\"text-align: justify;\">Bottom line: From SQL Server 2005 onward, if <em>DBCC CHECKDB<\/em> reports corruption, then at\u00a0the time that it ran there definitely was corruption. Make sure you don\u2019t just ignore the problem\u00a0as next time the corruption occurs, you may not be so \u2018lucky\u2019 that it just seemed to disappear.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Every [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30,108],"tags":[],"class_list":["post-4814","post","type-post","status-publish","format-standard","hentry","category-corruption","category-sql101"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: How can corruptions 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\/sqlskills-sql101-how-can-corruptions-disappear\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: How can corruptions disappear? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Every [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-17T19:23:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-12-31T01:37:00+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\/sqlskills-sql101-how-can-corruptions-disappear\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/\",\"name\":\"SQLskills SQL101: How can corruptions disappear? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-10-17T19:23:52+00:00\",\"dateModified\":\"2019-12-31T01:37:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: How can corruptions 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":"SQLskills SQL101: How can corruptions 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\/sqlskills-sql101-how-can-corruptions-disappear\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: How can corruptions disappear? - Paul S. Randal","og_description":"As\u00a0Kimberly blogged about earlier this year, SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Every [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/","og_site_name":"Paul S. Randal","article_published_time":"2017-10-17T19:23:52+00:00","article_modified_time":"2019-12-31T01:37:00+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\/sqlskills-sql101-how-can-corruptions-disappear\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/","name":"SQLskills SQL101: How can corruptions disappear? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-10-17T19:23:52+00:00","dateModified":"2019-12-31T01:37:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-corruptions-disappear\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: How can corruptions 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\/4814","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=4814"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4814\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}