{"id":11030,"date":"2018-03-20T11:14:46","date_gmt":"2018-03-20T10:14:46","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/"},"modified":"2018-03-20T11:14:46","modified_gmt":"2018-03-20T10:14:46","slug":"sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/","title":{"rendered":"SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?"},"content":{"rendered":"

Last week, I presented this option in our internal event, the dbi xChange.<\/p>\n

My colleague David Barbarin<\/a> asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild?<\/p>\n

I was not able to answer because I forgot to do this test.<\/p>\n

The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference<\/a>). I always use the option PAUSE to see how it works but never with a break.<\/p>\n

Now I do the test to have the answer. It’s never too late! \ud83d\ude09<\/p>\n

To do my demo, I downloaded the Microsoft example database WideWorldImportersDW from github<\/a>.<\/p>\n

I choose the index NCI_dbo_Transaction in the Transaction table.<\/p>\n

\"riro_00\"<\/a><\/p>\n

As you can see with this query, the index is big enough to have the time to interrupt the rebuild.<\/p>\n

I run the rebuild with this command:<\/p>\n

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] \nREBUILD WITH (RESUMABLE = ON, ONLINE = ON)\nGO\n<\/pre>\n
After killing the SQL Server task in the task manager, I have this message in SSMS:
\n\"riro_01\"<\/a>
\nOf course, the server is no more available\u2026<\/p>\n
I start the SQL Server service and have a look on the index state with the DMV sys.index_resumable_operations:
\n\"riro_02\"<\/a>
\nAs you can see the index is on state PAUSE with 19% completed.<\/p>\n
To restart, I run the rebuild with the RESUME option:
\n\"riro_03\"<\/a><\/p>\n
If the index rebuild is interrupted, you don\u2019t need to restart from scratch.
\nThe rebuild is in a PAUSE state and then you can decide or not to continue the rebuild.
\nVery clever option, isn’t it?<\/p>\n
Don\u2019t hesitate to go on David\u2019s blog to read how this option works with AlwaysOn:
\nhttps:\/\/www.dbi-services.com\/blog\/sql-server-2017-high-availability-and-resumable-online-indexes\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for […]<\/p>\n","protected":false},"author":15,"featured_media":11031,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,368,99,48],"tags":[49,67,51,1077],"type_dbi":[],"class_list":["post-11030","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-development-performance","category-sql-server","category-technology-survey","tag-microsoft","tag-performance","tag-sql-server","tag-sql-server-2017"],"acf":[],"yoast_head":"\nSQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild? - dbi Blog<\/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.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild?\" \/>\n<meta property=\"og:description\" content=\"Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-20T10:14:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1499\" \/>\n\t<meta property=\"og:image:height\" content=\"287\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?\",\"datePublished\":\"2018-03-20T10:14:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\"},\"wordCount\":298,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg\",\"keywords\":[\"Microsoft\",\"Performance\",\"SQL Server\",\"SQL Server 2017\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Development & Performance\",\"SQL Server\",\"Technology Survey\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\",\"name\":\"SQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg\",\"datePublished\":\"2018-03-20T10:14:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg\",\"width\":1499,\"height\":287},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild? - dbi Blog","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.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild?","og_description":"Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/","og_site_name":"dbi Blog","article_published_time":"2018-03-20T10:14:46+00:00","og_image":[{"width":1499,"height":287,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?","datePublished":"2018-03-20T10:14:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/"},"wordCount":298,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg","keywords":["Microsoft","Performance","SQL Server","SQL Server 2017"],"articleSection":["Database Administration & Monitoring","Development & Performance","SQL Server","Technology Survey"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/","name":"SQL Server 2017: What's happens with an interruption during a Resumable Online Index Rebuild? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg","datePublished":"2018-03-20T10:14:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/riro_00.jpg","width":1499,"height":287},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2017-whats-happens-with-an-interruption-during-a-resumable-online-index-rebuild\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11030","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11030"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11030\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/11031"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11030"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11030"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}