{"id":813,"date":"2009-08-03T07:19:00","date_gmt":"2009-08-03T07:19:00","guid":{"rendered":"\/blogs\/paul\/post\/Importance-of-running-regular-consistency-checks.aspx"},"modified":"2017-04-13T11:41:28","modified_gmt":"2017-04-13T18:41:28","slug":"importance-of-running-regular-consistency-checks","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/","title":{"rendered":"Importance of running regular consistency checks"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/weekly-survey-how-often-do-you-run-consistency-checks\/\"><font face=\"verdana,geneva\" size=\"2\">here<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> for the survey). Now I&#39;m back online again, and so here are the results as of 8\/3\/09. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">&nbsp;<img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg\" alt=\"\" width=\"577\" height=\"377\" \/> <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The results are actually surprising &#8211; I didn&#39;t expect so many people to be running consistency checks so frequently &#8211; 25% daily and another 37% weekly &#8211; very cool! <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The number of people who don&#39;t know what consistency checks are may look a little depressing but I think it&#39;s probably a symptom of the number of people coming into the SQL world as involuntary DBAs. For those people, consistency checks are a way of proactively checking for database corruption, which is nearly always caused by the I\/O subsystem. You can read a good introduction to consistency checking and other database maintenance topics in the article I wrote for TechNet Magazine in August 2008 &#8211; <\/font><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/2008.08.database.aspx\"><font face=\"verdana,geneva\" size=\"2\">Top Tips for Effective Database Maintenance<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">&nbsp;and bit more in the previous survey <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/\"><font face=\"verdana,geneva\" size=\"2\">Importance of how you run consistency checks<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Basically you need to run regular consistency checks. There&#39;s a myth that you don&#39;t need to run consistency checks &#8211; this was perpetuated by various marketing folks when SQL 7.0 shipped, because SQL 6.5 used to cause allocation corruptions and the rewrite for 7.0 removed all the corruption problems. Now, of course there have been bugs in SQL Server that cause problems, but they account for a tiny fraction of the corruptions out there. Nearly all corruptions are caused by something going wrong in the I\/O subsystem &#8211; and you can&#39;t predict when that will or won&#39;t happen. Jim Gray once likened the disk heads in a hard drive as akin to a 747 flying at 500mph 1\/4 inch above the ground &#8211; scary stuff. You DO need to run consistency checks, because corruptions do happen. You can read more about the causes of corruptions in this blog post: <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-26-myths-around-causing-corruption\/\"><font face=\"verdana,geneva\" size=\"2\">Search Engine Q&amp;A #26: Myths around causing corruption<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">So &#8211; how often should you run consistency checks? Well, it depends. When I&#39;m teaching I like to give two examples: <\/font>\n<\/p>\n<ul>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">You have a dodgy I\/O subsystem that is causing corruptions. You have no backups. You have a zero data-loss requirement. With no backups, your only way to get rid of corruptions is to run repair, which usually leads to data loss (see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-database-repair\/\"><font face=\"verdana,geneva\" size=\"2\">Misconceptions around database repair<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">). In that case, you want to know about corruption as soon as possible to limit the amount of data loss from running repair. Contrived example for sure, but you&#39;d be surprised what I&#39;ve seen&#8230; <\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">You have a rock-solid I\/O subsystem, with all drivers and firmware up-to-date. You have a comprehensive backup strategy which you&#39;ve extensively tested and you&#39;ve confident you can recover from corruptions with zero-to-minimal downtime and data loss. In this case you may be comfortable running consistency checks once a week, say. <\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The overwhelming factor in how often to run consistency checks is you. How comfortable are you with the integrity of your I\/O subsystem&nbsp;and your ability to recover from a corruption problem. If you have corruptions in your database today, you&#39;ll probably run DBCC CHECKDB on it every day for a month, right?!? <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Part of any database maintenance of high-availability strategy is proactively making sure that corruption doesn&#39;t exist in the database &#8211; otherwise when you DO discover it, it may be more pervasive and it will take you longer to recover, and potentially with more downtime. Therefore the answer really is that you should run it as often as you can. Sometimes that can be difficult for very large databases (your definition is likely to be different than mine &#8211; I think 500GB and larger &#8211; depends on your hardware etc) as CHECKDB can take a long time to run (see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-how-long-will-checkdb-take-to-run\/\"><font face=\"verdana,geneva\" size=\"2\">CHECKDB From Every Angle: How long will CHECKDB take to run?<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">), but there are ways you can effectively consistency check even a VLDB &#8211; see <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-consistency-checking-options-for-a-vldb\/\"><font face=\"verdana,geneva\" size=\"2\">CHECKDB From Every Angle: Consistency Checking Options for a VLDB<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">So &#8211; I&#39;m very pleased to see so many people running regular consistency checks. However, maybe I&#39;m just a pessimist but I did expect some of the less than optimal options to have higher numbers. Let&#39;s look at each in turn. <\/font>\n<\/p>\n<div>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Never.<\/strong> The only time this is acceptable is if you don&#39;t know what consistency checks are and why you need them. Enough said. Even if you think you *can&#39;t* run consistency checks because it takes too many resources\/time\/etc, take a look at the blog post I mention above. There&#39;s always a way.<\/font><\/font>\n<\/div>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Only when corruption is detected some other way.<\/strong> By the time corruption has occurred and is detected through regular operations, it&#39;s likely to be more pervasive. Many databases do not see all their data read\/updated as part of regular operations, which means that if a part of the database that&#39;s not used for a while gets corrupted, and you&#39;re not running consistency checks, you&#39;re not going to know. That means you&#39;re not going to know that your I\/O subsystem is causing problems &#8211; and so more corruption will occur. You might think this isn&#39;t a big deal, but it can be depending on what part of the database (or maybe system databases) gets corrupted. Some things can be restored or repaired relatively simply &#8211; but what if, say, the boot page of master is corrupted? Your entire instance is down until you sort that out. It&#39;s always better to proactively discover corruption before it hits you in a way that really messes you up.<\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Only during an event like an upgrade or migration. <\/strong>In this category I see people running consistency checks AFTER doing an upgrade but not before. From release to release, DBCC CHECKDB has improved &#8211; especially from 2000 to 2005, where system catalog consistency checks were added. If you run consistency checks after an upgrade and find the database is corrupt, what do you do? Run repair? Go back to the earlier version and run repair? Restore your backups? The odds are that if you&#39;ve got corruption, it&#39;s also in your backups too. Also, you may think that the upgrade itself caused the corruption &#8211; I&#39;ve never seen this. It&#39;s always been that the corruption was there before and only got discovered after the upgrade. Always run consistency checks BEFORE doing an upgrade &#8211; to make sure you&#39;ve got a chance of putting things right before (potentially irrevocably) moving to the newer version.<\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\"><strong>Only after performing a restore, or after a failover.<\/strong> This is, of course, a good practice, but shouldn&#39;t be the only time a consistency check is performed &#8211; for most of the same reasons as I&#39;ve explained above. What do you do if the database is found to be corrupt after restoring backups? You&#39;re looking at data loss now.<\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">To summarize: make sure you&#39;re running regular consistency checks &#8211; with the regularity in-line with your comfort zone and your maintenance\/high-availability strategies.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Next up &#8211; the next survey!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I&#39;m back online again, and so here are the results as of 8\/3\/09. &nbsp; The results are actually [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,31,52,91],"tags":[],"class_list":["post-813","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-database-maintenance","category-involuntary-dba","category-surveys"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Importance of running regular consistency checks - 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\/importance-of-running-regular-consistency-checks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Importance of running regular consistency checks - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I&#039;m back online again, and so here are the results as of 8\/3\/09. &nbsp; The results are actually [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-08-03T07:19:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg\" \/>\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=\"6 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\/importance-of-running-regular-consistency-checks\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/\",\"name\":\"Importance of running regular consistency checks - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg\",\"datePublished\":\"2009-08-03T07:19:00+00:00\",\"dateModified\":\"2017-04-13T18:41:28+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Importance of running regular consistency checks\"}]},{\"@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":"Importance of running regular consistency checks - 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\/importance-of-running-regular-consistency-checks\/","og_locale":"en_US","og_type":"article","og_title":"Importance of running regular consistency checks - Paul S. Randal","og_description":"A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I&#39;m back online again, and so here are the results as of 8\/3\/09. &nbsp; The results are actually [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/","og_site_name":"Paul S. Randal","article_published_time":"2009-08-03T07:19:00+00:00","article_modified_time":"2017-04-13T18:41:28+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/","name":"Importance of running regular consistency checks - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg","datePublished":"2009-08-03T07:19:00+00:00","dateModified":"2017-04-13T18:41:28+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/8\/howoftencheck.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-running-regular-consistency-checks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Importance of running regular consistency checks"}]},{"@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\/813","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=813"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/813\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}