{"id":1128,"date":"2007-11-14T07:07:50","date_gmt":"2007-11-14T07:07:50","guid":{"rendered":"\/blogs\/paul\/post\/CHECKDB-From-Every-Angle-Tips-and-tricks-for-interpreting-CHECKDB-output.aspx"},"modified":"2007-11-14T07:07:50","modified_gmt":"2007-11-14T07:07:50","slug":"checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/","title":{"rendered":"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output"},"content":{"rendered":"<p><FONT face=Verdana><FONT size=2><br \/>\n<P>Last week at SQL Connections someone said that CHECKDB\u2019s output is \u2018useless\u2019.&nbsp; Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there\u2019s a lot of truth in that statement \u2013 the error messages from CHECKDB do tell you exactly what\u2019s corrupt in the database but they\u2019re not exactly easy to read and understand unless you\u2019re intimately familiar with the on-disk structures used to store a SQL Server database. It\u2019s pretty easy to understand the output if there\u2019s only one or two error messages, but once you get past 10 or so it becomes extremely difficult to figure out what the actual problem is for normal people (i.e. not CHECKDB geeks like me, Ryan or a handful of senior people in Product Support)<\/P><br \/>\n<P>So, how are you supposed to figure out what\u2019s wrong? In this post I want to run through a few tips and tricks you can use.<\/P><br \/>\n<UL><br \/>\n<LI>If the recommended repair level (at the very bottom of the output) is <FONT face=\"Courier New\">REPAIR_REBUILD<\/FONT> then only non-clustered indexes are damaged. This meanscyou don\u2019t need to run repair (which needs the database in single-user mode) or restore from backups.&nbsp;You can just manually rebuild the damaged indexes to fix the problem, possibly even online if you&#8217;re running Enterprise Edition and the index doesn&#8217;t have any LOB columns.<br \/>\n<LI>There are a couple of cases where even if it\u2019s only non-clustered indexes that are damaged, <FONT face=\"Courier New\">REPAIR_ALLOW_DATA_LOSS<\/FONT> may still be the recommended repair option. In this case, check the index ID in all the error messages \u2013 if all index IDs are 2 or higher, then it\u2019s only non-clustered indexes that are damaged and you can rebuild them yourself instead of running repair\/restore.<br \/>\n<LI>Figure out a list of tables that are affected by looking at the summary messages about how many corruptions were found per table. If each of the tables is isolated on a separate filegroup then these filegroups can be taken offline and restored&nbsp;individually, possibly without interrupting the application workload. If there&#8217;s no backup, then you can run <FONT face=Arial>DBCC CHECKTABLE<\/FONT> with repair, for a faster repair operation than running a full CHECKDB.<br \/>\n<LI>Figure out the list of damaged pages. There are a number of error messages that could point to a damaged page \u2013 usually 8928 or 2537 \u2013 and then you can choose to do single-page restores from your backups to minimize downtime, possibly even online in Enterprise Edition.<br \/>\n<LI>Are there any errors that CHECKDB can\u2019t repair?&nbsp; If so, you have no choice but to restore from backups or to extract as much info as possible into a new database. Things to look for that mean repair won\u2019t be able to fix everything are:<br \/>\n<UL><br \/>\n<LI>CHECKDB stops early and complains about system table pre-checks failing (errors 7984 \u2013 7988 inclusive)<br \/>\n<LI>CHECKDB reports any metadata corruption (8992, 8995 errors)<br \/>\n<LI>CHECKDB reports any errors on PFS page headers (8939, 8946 errors with a possible 8998 error as well)<\/LI><\/UL><\/LI><\/UL><br \/>\n<P>While I was at Microsoft I wrote two 200-page+ documents detailing all the CHECKDB error messages for SQL Server 2000 and 2005 \u2013 the team says they should all be translated into Books Online entries by next summer \u2013 a bunch of them are there already. In the meantime, I hope the hints above will take a little of the mystery out of CHECKDB\u2019s output.<\/P><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last week at SQL Connections someone said that CHECKDB\u2019s output is \u2018useless\u2019.&nbsp; Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there\u2019s a lot of truth in that statement \u2013 the error messages from CHECKDB do tell you exactly what\u2019s corrupt in the database but they\u2019re not exactly [&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,30,31,34,35,74],"tags":[],"class_list":["post-1128","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-corruption","category-database-maintenance","category-dbcc","category-disaster-recovery","category-repair"],"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: Tips and tricks for interpreting CHECKDB output - 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-tips-and-tricks-for-interpreting-checkdb-output\/\" \/>\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: Tips and tricks for interpreting CHECKDB output - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Last week at SQL Connections someone said that CHECKDB\u2019s output is \u2018useless\u2019.&nbsp; Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there\u2019s a lot of truth in that statement \u2013 the error messages from CHECKDB do tell you exactly what\u2019s corrupt in the database but they\u2019re not exactly [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-11-14T07:07:50+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-tips-and-tricks-for-interpreting-checkdb-output\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/\",\"name\":\"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-11-14T07:07:50+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-tips-and-tricks-for-interpreting-checkdb-output\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output\"}]},{\"@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: Tips and tricks for interpreting CHECKDB output - 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-tips-and-tricks-for-interpreting-checkdb-output\/","og_locale":"en_US","og_type":"article","og_title":"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output - Paul S. Randal","og_description":"Last week at SQL Connections someone said that CHECKDB\u2019s output is \u2018useless\u2019.&nbsp; Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there\u2019s a lot of truth in that statement \u2013 the error messages from CHECKDB do tell you exactly what\u2019s corrupt in the database but they\u2019re not exactly [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/","og_site_name":"Paul S. Randal","article_published_time":"2007-11-14T07:07:50+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-tips-and-tricks-for-interpreting-checkdb-output\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/","name":"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-11-14T07:07:50+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-tips-and-tricks-for-interpreting-checkdb-output\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-tips-and-tricks-for-interpreting-checkdb-output\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output"}]},{"@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\/1128","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=1128"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1128\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}