{"id":4759,"date":"2017-07-19T12:15:45","date_gmt":"2017-07-19T19:15:45","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4759"},"modified":"2017-07-19T12:15:45","modified_gmt":"2017-07-19T19:15:45","slug":"sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/","title":{"rendered":"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption"},"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;\">An interesting situation was discussed online recently which prompted me to write this post. A fellow MVP was seeing periodic corruption messages in his error log, but <em>DBCC CHECKDB<\/em> on all databases didn&#8217;t find any corruptions. A subsequent restart of the instance caused the problem to go away.<\/p>\n<p style=\"text-align: justify;\">My diagnosis? Memory corruption. Something had corrupted a page in memory &#8211; maybe it was bad memory chips or a memory scribbler (something that writes into SQL Server&#8217;s buffer pool, like a poorly-written extended stored procedure), or maybe a SQL Server bug. Whatever it was, restarting the instance wiped the buffer pool clean, removing the corrupt page.<\/p>\n<p style=\"text-align: justify;\">So why didn&#8217;t <em>DBCC CHECKDB<\/em> encounter the corrupt page?<\/p>\n<p style=\"text-align: justify;\">The answer is to do with <em>DBCC CHECKDB<\/em>&#8216;s use of database snapshots (and all other <em>DBCC CHECK*<\/em> commands). It creates a database snapshot and then runs the consistency-checking algorithms on the database snapshot. The database snapshot is a transactionally-consistent, unchanging view of the database, which is what <em>DBCC CHECKDB<\/em> requires.<\/p>\n<p style=\"text-align: justify;\">More info on <em>DBCC CHECKDB<\/em>\u2019s use of snapshots, and potential problems can be found at:<\/p>\n<ul style=\"text-align: justify;\">\n<li>The first section of\u00a0<em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-complete-description-of-all-checkdb-stages\/\">CHECKDB From Every Angle: Complete description of all CHECKDB stages<\/a><\/em><\/li>\n<li><em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\">CHECKDB From Every Angle: Why would CHECKDB run out of space?<\/a><\/em><\/li>\n<li><em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/database-snapshots-when-things-go-wrong\/\">Database snapshots \u2013 when things go wrong<\/a><\/em><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">A database snapshot is a separate database as far as the buffer pool is concerned, with its own database ID. A page in the buffer pool is owned by exactly one database ID, and cannot be shared by any other databases. So when <em>DBCC CHECKDB<\/em> reads a page in the context of the database snapshot, that page must be read from the source database on disk; it cannot use the page from the source database if it&#8217;s already in memory, as that page has the wrong database ID.<\/p>\n<p style=\"text-align: justify;\">This means that <em>DBCC CHECKDB<\/em> reads the entire source database from disk when it uses a database snapshot. This is not a bad thing.<\/p>\n<p style=\"text-align: justify;\">This also means that if there&#8217;s a page in the source database that&#8217;s corrupt in memory but not corrupt on disk, <em>DBCC CHECKDB<\/em> will not encounter\u00a0it if it uses a database snapshot (the default).<\/p>\n<p style=\"text-align: justify;\">If you suspect that a database has some corruption in memory, the only way to have <em>DBCC CHECKDB<\/em> use the in-memory pages, is to use the <em>WITH TABLOCK<\/em> option, which skips using a database snapshot and instead uses locks to quiesce changes in the database.<\/p>\n<p style=\"text-align: justify;\">Hope this helps clear up any confusion!<\/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. An [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,30,33,35,108],"tags":[],"class_list":["post-4759","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-corruption","category-database-snapshots","category-disaster-recovery","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: Why DBCC CHECKDB can miss memory corruption - 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-why-dbcc-checkdb-can-miss-memory-corruption\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption - 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. An [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-19T19:15:45+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=\"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\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/\",\"name\":\"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-07-19T19:15:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption\"}]},{\"@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: Why DBCC CHECKDB can miss memory corruption - 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-why-dbcc-checkdb-can-miss-memory-corruption\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption - 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. An [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/","og_site_name":"Paul S. Randal","article_published_time":"2017-07-19T19:15:45+00:00","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\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/","name":"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-07-19T19:15:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-dbcc-checkdb-can-miss-memory-corruption\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Why DBCC CHECKDB can miss memory corruption"}]},{"@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\/4759","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=4759"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4759\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}