{"id":967,"date":"2008-12-17T11:03:00","date_gmt":"2008-12-17T11:03:00","guid":{"rendered":"\/blogs\/paul\/post\/TechEd-demo-nonclustered-index-corruption.aspx"},"modified":"2017-04-13T09:54:11","modified_gmt":"2017-04-13T16:54:11","slug":"teched-demo-nonclustered-index-corruption","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/","title":{"rendered":"TechEd demo: nonclustered index corruption"},"content":{"rendered":"

\nThis blog post describes the demo "2 – NC Indexes" 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>.<\/font>\n<\/p>\n

Edit 6\/4\/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to fix the corruption by doing a DROP + CREATE index, as all other methods will try to read the old index to build them. You can avoid any constraints (e.g. unique) being invalidated in between the DROP and the CREATE by wrapping the whole operation in an explicit transaction.<\/font><\/font> <\/p>\n

\nThe aim of this demo is to show that sometimes its just redundant data (i.e. nonclustered indexes) that get corrupted, and so you don't have to do anything that takes the actual data offline – like restoring from a full backup or running one of the repair options (both of which require the database to be in single-user mode).<\/font> <\/font>\n<\/p>\n

\nLet's look at an example. Extract and restore the DemoNCIndex database, and the NCIndexCorruption.sql script. What do we get from running DBCC CHECKDB<\/font> on the DemoNCIndex database (lines 39-42)?<\/font> <\/font>\n<\/p>\n

\n\t<\/p>\n

\n\tDBCC CHECKDB (<\/font>DemoNCIndex) <\/font>WITH<\/font> NO_INFOMSGS<\/font>,<\/font> ALL_ERRORMSGS<\/font><\/font>;
\n\t<\/font>GO <\/font>\n\t<\/p>\n

\n\tMsg 8951, Level 16, State 1, Line 1
\n\tTable error: table 'Customers' (ID 453576654). Data row does not have a matching index row in the index 'CustomerName' (ID 2). Possible missing or invalid keys for the index row matching:
\n\tMsg 8955, Level 16, State 1, Line 1
\n\tData row (1:45:28) identified by (CustomerID = 29) with index values 'LastName = 'Adams' and CustomerID = 29'.
\n\tMsg 8951, Level 16, State 1, Line 1
\n\tTable error: table 'Customers' (ID 453576654). Data row does not have a matching index row in the index 'CustomerName' (ID 2). Possible missing or invalid keys for the index row matching:
\n\tMsg 8955, Level 16, State 1, Line 1
\n\tData row (1:180:164) identified by (CustomerID = 2118) with index values 'LastName = 'Adams' and CustomerID = 2118'.<\/font> <\/font>\n\t<\/p>\n

\t<snip – removed for brevity><\/font> <\/font><\/p>\n

\n\tMsg 8952, Level 16, State 1, Line 1
\n\tTable error: table 'Customers' (ID 453576654). Index row in index 'CustomerName' (ID 2) does not match any data row. Possible extra or invalid keys for:
\n\tMsg 8956, Level 16, State 1, Line 1
\n\tIndex row (1:24482:16) with values (LastName = 'Andersen' and CustomerID = 18718) pointing to the data row identified by (CustomerID = 18718).
\n\tMsg 8952, Level 16, State 1, Line 1
\n\tTable error: table 'Customers' (ID 453576654). Index row in index 'CustomerName' (ID 2) does not match any data row. Possible extra or invalid keys for:
\n\tMsg 8956, Level 16, State 1, Line 1
\n\tIndex row (1:24482:127) with values (LastName = 'Arthur' and CustomerID = 9758) pointing to the data row identified by (CustomerID = 9758).
\n\t<\/font>CHECKDB found 0 allocation errors and 26 consistency errors in table 'Customers' (object ID 453576654).
\n\tCHECKDB found 0 allocation errors and 26 consistency errors in database 'DemoNCIndex'
\n\trepair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (DemoNCIndex).<\/font> <\/font> <\/font>\n\t<\/p>\n

\t<\/font><\/font>\n<\/p><\/blockquote>\n

\nLots of errors. Now, in this example there are only 26 errors, but in cases where there are hundreds of errors it can be really hard to tell whether all the corruptions are in nonclustered indexes (i.e. indexes with IDs > 1). Luckily, there's an undocumented option to all the DBCC CHECK*<\/font> commands – WITH TABLERESULTS<\/font>. The option is undocumented because the output can change from release to release, but basically this converts the DBCC output into tabular form. Try running lines 48-50 in the script and you'll see what I mean. One of the columns in the output is IndexId – so you can easily see whether all the errors are in nonclustered indexes. In this case, they are, and all in one index of the Customers table.<\/font> <\/font>\n<\/p>\n

\nYou could run lines 55-57 of the script to prove to yourself that repairs can't be run online, and then realize that we can address the problem without having to run repair or restore. First off we need to figure out the name of the index to fix – index ID 2 of the Customers table. Lines 77-80 run sp_HelpIndex<\/font> on the table (although I should really be using Kimberly's <\/font>sp_HelpIndex2<\/font><\/a>): <\/font>\n<\/p>\n

\n

\n\tUSE<\/font> DemoNCIndex
\n\tGO
\n\tEXEC<\/font> sp_HelpIndex<\/font> 'Customers'<\/font><\/font>;
\n\t<\/font>GO<\/font><\/font> <\/font>\n\t<\/p>\n

\n\tindex_name     index_description                                   index_keys
\n\t————– ————————————————— ————
\n\tCustomerName   nonclustered located on PRIMARY                     LastName
\n\tCustomerPK     clustered, unique, primary key located on PRIMARY   CustomerID<\/font> <\/font>\n\t<\/p>\n<\/blockquote>\n

<\/font><\/p>\n

\nThe nonclustered index is called CustomerName. Plug in the index name to line 82, then try fixing the index by doing an online index rebuild, and run DBCC CHECKDB<\/font> afterwards (lines 82-89). The corruption hasn't been fixed! Online index rebuild reads the old<\/em> index to build the new one so the new index has the same missing rows as the old one. We need to do an offline<\/em> index rebuild – with lines 110-115. After the last DBCC CHECKDB<\/font>, the index is fixed up. Now, on SQL Server 2008, you may or may not get a query plan for the index rebuild that doesn't use the old index, because the query optimizer has some more plan choices available to it – so on SQL Server 2008 you may need to do an actual drop and create of the broken index (carefully, if its enforcing a constraint). <\/font>\n<\/p>\n

\nSo – just because DBCC CHECKDB<\/font> reports a ton of errors, that doesn't necessarily mean that the database needs to be taken (essentially) offline to repair it – check through the errors to see if it's just nonclustered indexes that are affected.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

This blog post describes the demo "2 – NC Indexes" 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. Edit 6\/4\/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]<\/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,31,34,35,38,47,74],"tags":[],"class_list":["post-967","post","type-post","status-publish","format-standard","hentry","category-checkdb-from-every-angle","category-conferences","category-corruption","category-database-maintenance","category-dbcc","category-disaster-recovery","category-example-scripts","category-indexes-from-every-angle","category-repair"],"yoast_head":"\nTechEd demo: nonclustered index 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\/teched-demo-nonclustered-index-corruption\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TechEd demo: nonclustered index corruption - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This blog post describes the demo "2 – NC Indexes" 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. Edit 6\/4\/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-12-17T11:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:11+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=\"5 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-nonclustered-index-corruption\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/\",\"name\":\"TechEd demo: nonclustered index corruption - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-12-17T11:03:00+00:00\",\"dateModified\":\"2017-04-13T16:54:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TechEd demo: nonclustered index 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":"TechEd demo: nonclustered index 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\/teched-demo-nonclustered-index-corruption\/","og_locale":"en_US","og_type":"article","og_title":"TechEd demo: nonclustered index corruption - Paul S. Randal","og_description":"This blog post describes the demo "2 – NC Indexes" 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. Edit 6\/4\/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/","og_site_name":"Paul S. Randal","article_published_time":"2008-12-17T11:03:00+00:00","article_modified_time":"2017-04-13T16:54:11+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/","name":"TechEd demo: nonclustered index corruption - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-12-17T11:03:00+00:00","dateModified":"2017-04-13T16:54:11+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/teched-demo-nonclustered-index-corruption\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"TechEd demo: nonclustered index 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\/967","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=967"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/967\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}