{"id":501,"date":"2012-04-05T14:07:00","date_gmt":"2012-04-05T14:07:00","guid":{"rendered":"\/blogs\/paul\/post\/Importance-of-how-you-run-consistency-checks.aspx"},"modified":"2017-08-03T15:50:08","modified_gmt":"2017-08-03T22:50:08","slug":"importance-of-how-you-run-consistency-checks","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/","title":{"rendered":"Importance of how you run consistency checks"},"content":{"rendered":"<p style=\"text-align: justify;\">Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies &#8211; hoping that more people were offloading consistency checks in concert with testing backups.<\/p>\n<p style=\"text-align: justify;\">The results are very interesting.<\/p>\n<p style=\"text-align: justify;\"><strong>2009 survey results: <\/strong><\/p>\n<p style=\"text-align: justify;\"><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg\" alt=\"\" width=\"578\" height=\"337\" \/><\/p>\n<p style=\"text-align: justify;\"><strong>2012 survey results:<\/strong><\/p>\n<p style=\"text-align: justify;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2012\/4\/checkdbsurvey2.jpg\" alt=\"\" \/><\/p>\n<p style=\"text-align: justify;\">The results are almost *exactly* the same. This is quite surprising to me as I expected more people to be offloading the consistency checks because of resource constraints on the production systems. However, it does show that my previous survey was statistically accurate, even with only 67 responses.<\/p>\n<p style=\"text-align: justify;\">The Other results for this year&#8217;s survey don&#8217;t really change the distribution of answers, but add a few percent to the first answer. They are:<\/p>\n<ul style=\"text-align: justify;\">\n<li>11 x\u00a0<em>DBCC<\/em> with <em>NO_INFOMSGS,\u00a0ALL_ERRORMSGS<\/em> on production database.<\/li>\n<li>8 x Combination of 1 and 2.<\/li>\n<li>7 x Run\u00a0<em>DBCC CHECKDB<\/em>\u00a0with\u00a0<em>NO_INFOMSGS<\/em> on the production database.<\/li>\n<li style=\"text-align: justify;\">5 x Combination of 1 and 3. where maintenance windows permit\u00a0<em>DBCC CHECKDB<\/em>\u00a0with no options on the production server, otherwise on a restored backup on another server.<\/li>\n<li>4 x It depends.<\/li>\n<li>3 x A mix of\u00a0<em>PHYSICAL_ONLY<\/em> and\u00a0<em>DATA_PURITY<\/em> depending on server and day of week.<\/li>\n<li>3 x Run <em>DBCC CHECKDB<\/em> with no options after backup and restore to a test environment.<\/li>\n<li>2 x <em>DBCC CHECKDB WITH\u00a0DATA_PURITY<\/em> on production database.<\/li>\n<li>Combination of option 1, 2 and.<\/li>\n<li>Most are option 1, a few option 2, and we are considering\u00a0<em>CHECKTABLE<\/em> for our largest instance<\/li>\n<li>Mostly <em>CHECKDB<\/em> with <em>PHYSICAL_ONLY<\/em>; one instance with <em>CHECKTABLE<\/em> over multiple days.<\/li>\n<li>What is <em>DBCC CHECKDB<\/em><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Any of the answers where <em>DBCC CHECKDB<\/em> is being run on a continually updating copy of a database (e.g. through a database mirror, SAN mirror, log-shipping or Availability Group secondary) are incorrect. This method tells you nothing about the state of the main database on the production system as two different I\/O subsystems are involved. I&#8217;ve discussed this many times before so I won&#8217;t labor the point, but you either have to run the consistency checks on the production database, or on a restored backup of it, or you&#8217;re not testing the production database.\u00a0Nothing else is good enough.<\/p>\n<p style=\"text-align: justify;\">For the people using <em>BACKUP &#8230; WITH CHECKSUM<\/em> instead of doing regular consistency checks, you&#8217;re running the risk of bad memory chips corrupting your database &#8211; see <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2730-use-backup-with-checksum-to-replace-dbcc-checkdb\/\" target=\"_blank\" rel=\"noopener noreferrer\">A SQL Server DBA myth a day: (27\/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB<\/a><\/em>.<\/p>\n<p style=\"text-align: justify;\">Thanks to all who participated in the survey!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies &#8211; hoping that more people were offloading consistency checks in concert with testing backups. The results are very interesting. 2009 survey results: 2012 survey results: The results are [&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,91],"tags":[],"class_list":["post-501","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-database-maintenance","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 how you run 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-how-you-run-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 how you run consistency checks - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies &#8211; hoping that more people were offloading consistency checks in concert with testing backups. The results are very interesting. 2009 survey results: 2012 survey results: The results are [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-04-05T14:07:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-08-03T22:50:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.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=\"2 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-how-you-run-consistency-checks\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/\",\"name\":\"Importance of how you run consistency checks - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg\",\"datePublished\":\"2012-04-05T14:07:00+00:00\",\"dateModified\":\"2017-08-03T22:50:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg\",\"contentUrl\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Importance of how you run 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 how you run 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-how-you-run-consistency-checks\/","og_locale":"en_US","og_type":"article","og_title":"Importance of how you run consistency checks - Paul S. Randal","og_description":"Back in 2009 I ran a survey about methods of running consistency checks. I recently re-ran the survey to get a better idea of the prevailing methodologies &#8211; hoping that more people were offloading consistency checks in concert with testing backups. The results are very interesting. 2009 survey results: 2012 survey results: The results are [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/","og_site_name":"Paul S. Randal","article_published_time":"2012-04-05T14:07:00+00:00","article_modified_time":"2017-08-03T22:50:08+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg","type":"","width":"","height":""}],"author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/","name":"Importance of how you run consistency checks - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg","datePublished":"2012-04-05T14:07:00+00:00","dateModified":"2017-08-03T22:50:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-content\/uploads\/2009\/3\/surveycheckmethod.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/importance-of-how-you-run-consistency-checks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Importance of how you run 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\/501","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=501"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/501\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}