{"id":1047,"date":"2008-06-09T15:54:32","date_gmt":"2008-06-09T15:54:32","guid":{"rendered":"\/blogs\/paul\/post\/CHECKDB-From-Every-Angle-Using-DBCC-PAGE-to-find-what-repair-will-delete.aspx"},"modified":"2008-06-09T15:54:32","modified_gmt":"2008-06-09T15:54:32","slug":"checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/","title":{"rendered":"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete"},"content":{"rendered":"


\n

(I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…)<\/EM><\/P>
\n

Here’s a question that came up on of the SQLServerCentral.com<\/A> corruption forums I monitor that I think is worth blogging about. To paraphrase:<\/P>
\n


\n

I have a bunch of corruptions in a database, that look like they’ve been there for a while. Repair is my only option – it works but I’d like to know what data is being deleted. How can I do that? Here are some of the errors:<\/EM><\/P>
\n

Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168576) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168577) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168578) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168579) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168580) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168581) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 645577338, index ID 0: Page (1:168582) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Page (1:168576) was not seen in the scan although its parent (1:165809) and previous (1:168575) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 645577338, index ID 1. Page (1:168583) is missing a reference from previous page (1:168582). Possible chain linkage problem.<\/FONT><\/P><\/BLOCKQUOTE>
\n

This is a clustered index that CHECKDB  will repair by deleting pages at the leaf-level – essentially deleting a bunch of records. The pages look to be trashed (there were a bunch more errors that I didn’t include here that said the page headers were all corrupted – looked like the IO subsystem trashde a whole 64KB chunk of the disk) so there’s nothing much else you can do. As the table has a clustered index, you can use the error messages to find the pages on either ‘logical’ side of the pages being deleted – and hence figure out the range of records that have been deleted.

The errors show that pages 168576 through 168582 in file 1 are corrupt. There are also errors that say the previous page of 168576 is 168575, and the next page of 168582 is 168583. If you do a DBCC PAGE<\/FONT> of these two pages, you can find the lower and upper bound of the clustered index key values that have been lost. Think of three ranges:<\/FONT><\/FONT><\/P>
\n


    \n
  • the lower range of records that are intact, logically before the corrupt pages in the index<\/FONT><\/FONT>
    \n
  • the range of records that will be deleted by repair<\/FONT><\/FONT>
    \n
  • the upper range of records that are intact, logically after the corrupt pages in the index<\/FONT><\/FONT><\/LI><\/UL>
    \n

    To find the upper bound of the lower range:<\/P>
    \n


    \n

    DBCC<\/FONT><\/FONT> TRACEON (<\/FONT>3604);<\/FONT> — allows the output to come to the console
    <\/FONT><\/FONT>DBCC<\/FONT><\/FONT> PAGE (<\/FONT>‘dbname’<\/FONT>,<\/FONT> 1,<\/FONT> 168575,<\/FONT> 3);
    <\/FONT><\/FONT>GO<\/FONT><\/P><\/BLOCKQUOTE>
    \n

    <\/FONT>The key value in the slot at the end of output is the upper bound of the bottom range that’s intact. <\/P>
    \n

    Then do:<\/P>
    \n


    \n

    DBCC<\/FONT><\/FONT> PAGE (<\/FONT>‘dbname’<\/FONT>,<\/FONT> 1,<\/FONT> 168583,<\/FONT> 3);
    <\/FONT><\/FONT>GO<\/FONT><\/FONT><\/P><\/BLOCKQUOTE>
    \n

    The key value in the slot at the beginning of the output is the lower bound of the upper range that’s intact.

    Everything in the middle will be deleted. You could also try a DBCC PAGE<\/FONT> on the corrupt pages themselves too – you might be able to see some data in them.<\/P>
    \n

    I’ll be blogging a bunch more about repair after my corruption session this week at TechEd – watch this space!<\/FONT><\/P><\/FONT><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"

    (I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]<\/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,34,35,62,74,100],"tags":[],"class_list":["post-1047","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-corruption","category-dbcc","category-disaster-recovery","category-on-disk-structures","category-repair","category-undocumented-commands"],"yoast_head":"\nCHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete - 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-using-dbcc-page-to-find-what-repair-will-delete\/\" \/>\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: Using DBCC PAGE to find what repair will delete - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-06-09T15:54:32+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-using-dbcc-page-to-find-what-repair-will-delete\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/\",\"name\":\"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-06-09T15:54:32+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-using-dbcc-page-to-find-what-repair-will-delete\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete\"}]},{\"@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: Using DBCC PAGE to find what repair will delete - 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-using-dbcc-page-to-find-what-repair-will-delete\/","og_locale":"en_US","og_type":"article","og_title":"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete - Paul S. Randal","og_description":"(I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/","og_site_name":"Paul S. Randal","article_published_time":"2008-06-09T15:54:32+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-using-dbcc-page-to-find-what-repair-will-delete\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/","name":"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-06-09T15:54:32+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-using-dbcc-page-to-find-what-repair-will-delete\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/checkdb-from-every-angle-using-dbcc-page-to-find-what-repair-will-delete\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete"}]},{"@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\/1047","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=1047"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1047\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}