{"id":972,"date":"2008-11-30T17:37:00","date_gmt":"2008-11-30T17:37:00","guid":{"rendered":"\/blogs\/paul\/post\/TechEd-demo-corruptions-fatal-to-DBCC-CHECKDB.aspx"},"modified":"2013-04-03T05:41:56","modified_gmt":"2013-04-03T12:41:56","slug":"teched-demo-corruptions-fatal-to-dbcc-checkdb","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/","title":{"rendered":"TechEd demo: corruptions fatal to DBCC CHECKDB"},"content":{"rendered":"

This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post<\/a>.<\/span><\/p>\n

The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB<\/span> just cannot run on it. In that case, there’s no way to force DBCC CHECKDB<\/span> to get past the fatal corruption and so there’s no way to run a repair either – you’re looking at restoring from a backup or at worst, extracting as much data as possible into a new database.<\/span><\/p>\n

Let’s look at a couple of examples. Extract and restore the DemoFatalCorruption1 and DemoFatalCorruption2 databases, and the FatalErrors.sql script. What do we get from running DBCC CHECKDB<\/span> on DemoFatalCorruption1 (lines 47-49 in the script)?<\/span><\/p>\n

DBCC CHECKDB <\/span>(<\/span>DemoFatalCorruption1)<\/span>
\nWITH NO_INFOMSGS<\/span>,<\/span> ALL_ERRORMSGS<\/span>;<\/span>
\nGO<\/span><\/p>\n

 <\/p>\n

Msg 8928, Level 16, State 6, Line 1
\nObject ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:71) could not be processed. See other errors for details.
\n<\/span>CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
\nMsg 8906, Level 16, State 1, Line 1
\nPage (1:19) in database ID 8 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags ‘MIXED_EXT ALLOCATED 0_PCT_FULL’.
\nMsg 2575, Level 16, State 1, Line 1
\nThe Index Allocation Map (IAM) page (1:71) is pointed to by the next pointer of IAM page (0:0) in object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), but it was not detected in the scan.
\nMsg 7965, Level 16, State 2, Line 1
\nTable error: Could not check object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data) due to invalid allocation (IAM) page(s).
\nMsg 8906, Level 16, State 1, Line 1
\nPage (1:71) in database ID 8 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags ‘IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL’.
\nMsg 8939, Level 16, State 5, Line 1
\nTable error: Object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), page (1:71). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
\nMsg 8939, Level 16, State 6, Line 1
\nTable error: Object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), page (1:71). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
\nMsg 8939, Level 16, State 5, Line 1
\nTable error: Object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), page (1:71). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
\nMsg 8939, Level 16, State 6, Line 1
\nTable error: Object ID 15, index ID 1, partition ID 983040, alloc unit ID 983040 (type In-row data), page (1:71). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
\n<\/span>CHECKDB found 5 allocation errors and 3 consistency errors in table ‘sys.syshobts’ (object ID 15).
\nMsg 7995, Level 16, State 1, Line 1
\nDatabase ‘DemoFatalCorruption1’: consistency errors in system catalogs prevent further DBCC checkdb processing.
\n<\/span>CHECKDB found 0 allocation errors and 1 consistency errors in table ‘ALLOCATION’ (object ID 99).
\nCHECKDB found 6 allocation errors and 4 consistency errors in database ‘DemoFatalCorruption1’.<\/span><\/p>\n

 <\/p><\/blockquote>\n

A bunch of errors that look like regular DBCC CHECKDB<\/span> output – but if you look carefully\u00a0near the end of the output you’ll see error 7995 stating that the system catalogs are so corrupt that DBCC CHECKDB<\/span> can’t continue. Notice also that there’s nothing at the end of the output stating what the minimum repair level is to fix the errors – because repair cannot be run on this database.<\/span><\/p>\n

The second example is even worse (running lines 53-55 in the script):<\/span><\/p>\n

DBCC CHECKDB <\/span>(<\/span>DemoFatalCorruption2)<\/span>
\nWITH NO_INFOMSGS<\/span>,<\/span> ALL_ERRORMSGS<\/span>;<\/span>
\nGO <\/span><\/p>\n

Msg 211, Level 23, State 51, Line 1
\nPossible schema corruption. Run DBCC CHECKCATALOG.<\/p>\n

 <\/p><\/blockquote>\n

In this case, the corruption is so bad that DBCC CHECKDB<\/span> didn’t even get a chance to terminate gracefully – the metadata subsystem in the Query Processor just blew away the whole command. Running DBCC CHECKCATALOG<\/span> as the error message states doesn’t do any better – it just prints the same error! (I didn’t write that error message :-) <\/span><\/p>\n

So – just because DBCC CHECKDB<\/span> completes, doesn’t always mean it completes successfully<\/em>. Make sure you always check the output.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,27,30,34,35,38],"tags":[],"class_list":["post-972","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-conferences","category-corruption","category-dbcc","category-disaster-recovery","category-example-scripts"],"yoast_head":"\nTechEd demo: corruptions fatal to DBCC CHECKDB - 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\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TechEd demo: corruptions fatal to DBCC CHECKDB - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-11-30T17:37:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-03T12:41:56+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\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/\",\"name\":\"TechEd demo: corruptions fatal to DBCC CHECKDB - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-11-30T17:37:00+00:00\",\"dateModified\":\"2013-04-03T12:41:56+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TechEd demo: corruptions fatal to DBCC CHECKDB\"}]},{\"@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":"TechEd demo: corruptions fatal to DBCC CHECKDB - 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\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/","og_locale":"en_US","og_type":"article","og_title":"TechEd demo: corruptions fatal to DBCC CHECKDB - Paul S. Randal","og_description":"This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/","og_site_name":"Paul S. Randal","article_published_time":"2008-11-30T17:37:00+00:00","article_modified_time":"2013-04-03T12:41:56+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\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/","name":"TechEd demo: corruptions fatal to DBCC CHECKDB - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-11-30T17:37:00+00:00","dateModified":"2013-04-03T12:41:56+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-corruptions-fatal-to-dbcc-checkdb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"TechEd demo: corruptions fatal to DBCC CHECKDB"}]},{"@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\/972","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=972"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/972\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}