{"id":1134,"date":"2007-11-06T22:23:12","date_gmt":"2007-11-06T22:23:12","guid":{"rendered":"\/blogs\/paul\/post\/CHECKDB-From-Every-Angle-Why-would-CHECKDB-run-out-of-space.aspx"},"modified":"2017-04-13T09:52:05","modified_gmt":"2017-04-13T16:52:05","slug":"checkdb-from-every-angle-why-would-checkdb-run-out-of-space","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/","title":{"rendered":"CHECKDB From Every Angle: Why would CHECKDB run out of space?"},"content":{"rendered":"<p>This is a question that came up yesterday in our Disaster Recovery class so I&#8217;m typing it up in between attending sessions at Microsoft Day here at the conference. It&#8217;s an interesting experience watching all the Microsoft\u00a0speakers walking around in the distinctive blue shirts and no longer having to wear one myself.<\/p>\n<p>The question is the following &#8211; why does <em>DBCC CHECKDB<\/em> terminate with an out-of-space error in SQL Server 2005? A customer had a 500GB database spread over 17 LUNs on a SAN, with each LUN having only 5GB free. There&#8217;s a heavy concurrent workload running while <em>DBCC CHECKDB<\/em> is running and very often it doesn&#8217;t complete but instead stops with an error. What&#8217;s going on?<\/p>\n<p>The reason for this is the way that <em>DBCC CHECKDB<\/em> gets a transactionally consistent view of the database. In a nutshell, it creates a internal database snapshot of the database and then checks the database snapshot (you can read more about this in\u00a0my <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-complete-description-of-all-checkdb-stages\/\">previous post<\/a> detailing all the\u00a0steps of <em>DBCC CHECKDB<\/em>). A database snapshot needs to have one snapshot file for each file in the source database. In the case of <em>DBCC CHECKDB<\/em>, the snapshot files are created in the same place as the\u00a0existing database files &#8211; i.e. stored on the same disk volume &#8211; and you have no control over this. This means any changes to the database while <em>DBCC CHECKDB<\/em> is running will cause these snapshot files\u00a0to grow.<\/p>\n<p>If there&#8217;s a significant concurrent workload while <em>DBCC CHECKDB<\/em> is running, then these snapshot files\u00a0can get very big very quickly. In the case described in the question, the workload caused one of the snapshto files\u00a0to take up all available space on the LUN and then it ran out of space. When this happens the snapshot is no longer valid and so <em>DBCC CHECKDB<\/em> has to stop.<\/p>\n<p>There are two solutions to this. The obvious first one is to run <em>DBCC CHECKDB<\/em> in a period with low concurrent workload. That&#8217;s not feasible for many 24&#215;7 shops so the better solution is to create your own database snapshot (so you can control the placement of it) and then run <em>DBCC CHECKDB<\/em> on that. This is no different than having <em>DBCC CHECKDB<\/em> create its own snapshot.<\/p>\n<p>There&#8217;s another reason that <em>DBCC CHECKDB<\/em> may stop with an out-of-space error.\u00a0 <em>DBCC CHECKDB<\/em> has to validate the contents of the whole database. As such it needs to store info about things it has seen at one point during database scanning so that it can match that info against things it sees at later points during database scanning. These bits of info are stored in an in-memory worktable. In many cases though, the amount of info being stored exceeds the available memory and the worktable needs to spill over to tempdb. (For example, running <em>DBCC CHECKDB<\/em> against a 1TB database on a machine with 16GB of memory &#8211; the amount of info that <em>DBCC CHECKDB<\/em> needs to store the intermediate pieces of info will likely exceed the memory available to SQL Server). So &#8211; if tempdb isn&#8217;t big enough to store the worktable, it has to grow. If it can&#8217;t grow, then <em>DBCC CHECKDB<\/em> will fail because it needs the worktable to operate.<\/p>\n<p>You can check in advance how much (estimated) space will be needed by <em>DBCC CHECKDB<\/em> by running it using the <em>WITH ESTIMATEONLY<\/em> option (broken in 2008 R2, fixed in 2012 SP2 and 2014) and then making sure that tempdb is sized accordingly to accommodate the <em>DBCC CHECKDB<\/em> requirements AND the regular tempdb space requirements. See\u00a0<a href=\"https:\/\/msdn.microsoft.com\/Areas\/Epx\/Content\/500.htm?aspxerrorpath=\/en-us\/library\/ms345368.aspx\">Capacity Planning for tempdb<\/a> in Books Online for more info.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is a question that came up yesterday in our Disaster Recovery class so I&#8217;m typing it up in between attending sessions at Microsoft Day here at the conference. It&#8217;s an interesting experience watching all the Microsoft\u00a0speakers walking around in the distinctive blue shirts and no longer having to wear one myself. The question is [&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],"tags":[],"class_list":["post-1134","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>CHECKDB From Every Angle: Why would CHECKDB run out of space? - 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\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CHECKDB From Every Angle: Why would CHECKDB run out of space? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is a question that came up yesterday in our Disaster Recovery class so I&#8217;m typing it up in between attending sessions at Microsoft Day here at the conference. It&#8217;s an interesting experience watching all the Microsoft\u00a0speakers walking around in the distinctive blue shirts and no longer having to wear one myself. The question is [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-11-06T22:23:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:52:05+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\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\",\"name\":\"CHECKDB From Every Angle: Why would CHECKDB run out of space? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-11-06T22:23:12+00:00\",\"dateModified\":\"2017-04-13T16:52:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CHECKDB From Every Angle: Why would CHECKDB run out of space?\"}]},{\"@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":"CHECKDB From Every Angle: Why would CHECKDB run out of space? - 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\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/","og_locale":"en_US","og_type":"article","og_title":"CHECKDB From Every Angle: Why would CHECKDB run out of space? - Paul S. Randal","og_description":"This is a question that came up yesterday in our Disaster Recovery class so I&#8217;m typing it up in between attending sessions at Microsoft Day here at the conference. It&#8217;s an interesting experience watching all the Microsoft\u00a0speakers walking around in the distinctive blue shirts and no longer having to wear one myself. The question is [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/","og_site_name":"Paul S. Randal","article_published_time":"2007-11-06T22:23:12+00:00","article_modified_time":"2017-04-13T16:52:05+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\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/","name":"CHECKDB From Every Angle: Why would CHECKDB run out of space? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-11-06T22:23:12+00:00","dateModified":"2017-04-13T16:52:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-why-would-checkdb-run-out-of-space\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CHECKDB From Every Angle: Why would CHECKDB run out of space?"}]},{"@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\/1134","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=1134"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1134\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}