{"id":1162,"date":"2025-07-30T09:00:00","date_gmt":"2025-07-30T14:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1162"},"modified":"2025-07-29T22:53:19","modified_gmt":"2025-07-30T03:53:19","slug":"resumable-index-constraint-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1162","title":{"rendered":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT"},"content":{"rendered":"<h3><strong>Introduction&gt;<\/strong><\/h3>\n<p>Large tables make routine maintenance risky. Traditional online index builds and <code>ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT<\/code> operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).<\/p>\n<p><strong>1\u00a0\u00b7\u00a0Why Resumability Matters<\/strong><\/p>\n<ul>\n<li><strong>Smaller log footprint<\/strong>\u00a0\u2014\u00a0Each ~100\u202f000\u2011row batch commits independently, allowing the log to truncate.<\/li>\n<li><strong>Recovery from interruption<\/strong>\u00a0\u2014\u00a0Pause or fail\u2011over preserves progress; resume continues from last batch.<\/li>\n<li><strong>Controlled windows<\/strong>\u00a0\u2014\u00a0<code>MAX_DURATION<\/code> pauses work automatically after the specified time.<\/li>\n<li><strong>Reduced blocking<\/strong>\u00a0\u2014\u00a0Only a brief schema\u2011modification lock at final switch\u2011over.<\/li>\n<\/ul>\n<p><strong>2\u00a0\u00b7\u00a0Resumable Online Index Rebuilds<\/strong><br \/><strong>Version support<\/strong>\u00a0\u2013\u00a0SQL\u00a0Server\u00a02017+ (row\u2011store); SQL\u00a0Server\u00a02022 adds non\u2011clustered columnstore.<br \/><strong>Edition requirement<\/strong>\u00a0\u2013\u00a0Enterprise Edition on\u2011prem; Azure SQL supports resumable rebuild.<\/p>\n<p><strong>2.1 Internal Flow<\/strong><\/p>\n<ul>\n<li>Parse statement with <code>ONLINE = ON<\/code> and <code>RESUMABLE = ON<\/code>.<\/li>\n<li>Engine reads ~100\u202f000 rows into an internal batch and commits.<\/li>\n<li>Repeat until all data is processed or paused, failed, or timed out.<\/li>\n<li>On <code>RESUME<\/code>, engine reads metadata and restarts at next batch.<\/li>\n<li>Final phase acquires a brief SCH\u2011M lock to swap in the new index.<\/li>\n<\/ul>\n<p><strong>2.2 DDL and Control<\/strong><\/p>\n<pre><code>ALTER INDEX IX_OrderDate ON dbo.SalesFact\n    REBUILD WITH (\n        ONLINE       = ON,\n        RESUMABLE    = ON,\n        MAX_DURATION = 90,\n        MAXDOP       = 4\n    );\n-- Pause after current batch\nALTER INDEX IX_OrderDate ON dbo.SalesFact PAUSE;\n-- Resume with lower DOP\nALTER INDEX IX_OrderDate ON dbo.SalesFact\n    RESUME WITH (MAXDOP = 2, MAX_DURATION = 60);\n-- Abort and roll back if required\nALTER INDEX IX_OrderDate ON dbo.SalesFact ABORT;<\/code><\/pre>\n<p><strong>2.3 Monitoring<\/strong><\/p>\n<pre><code>SELECT name,\n       state_desc,           -- RUNNING | PAUSED | ABORTED\n       percent_complete,\n       total_elapsed_time\/1000 AS seconds\nFROM sys.index_resumable_operations;<\/code><\/pre>\n<p><strong>3\u00a0\u00b7\u00a0Resumable Online Index Creation<\/strong><br \/><strong>Version support<\/strong>\u00a0\u2013\u00a0SQL\u00a0Server\u00a02019+ (row\u2011store); SQL\u00a0Server\u00a02022 adds non\u2011clustered columnstore.<br \/><strong>Edition requirement<\/strong>\u00a0\u2013\u00a0Enterprise Edition on\u2011prem; Azure SQL supports resumable create.<\/p>\n<pre><code>CREATE INDEX IX_CustomerDate\n    ON dbo.SalesFact(OrderDate)\n    INCLUDE (CustomerName, Amount)\n    WITH (\n        ONLINE       = ON,\n        RESUMABLE    = ON,\n        MAX_DURATION = 60\n    );<\/code><\/pre>\n<p>Pause, resume, abort, and monitoring use the same commands and DMV as rebuilds.<\/p>\n<p><strong>4\u00a0\u00b7\u00a0Resumable <code>ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT<\/code> (PK, UNIQUE)<\/strong><br \/><strong>Version support<\/strong>\u00a0\u2013\u00a0SQL\u00a0Server\u00a02022.<br \/><strong>Edition requirement<\/strong>\u00a0\u2013\u00a0Enterprise Edition on\u2011prem.<\/p>\n<p><strong>4.1 Syntax<\/strong><\/p>\n<pre><code>ALTER TABLE dbo.SalesFact\n    ADD CONSTRAINT PK_SalesFact_Id PRIMARY KEY CLUSTERED (Id)\n    WITH (\n        ONLINE       = ON,\n        RESUMABLE    = ON,\n        MAX_DURATION = 120\n    );<\/code><\/pre>\n<p><strong>4.2 Control Commands<\/strong><\/p>\n<pre><code>-- Pause hidden index build\nALTER INDEX ALL ON dbo.SalesFact PAUSE;\n-- Resume\nALTER INDEX ALL ON dbo.SalesFact\n    RESUME WITH (MAX_DURATION = 60);\n-- Abort\nALTER INDEX ALL ON dbo.SalesFact ABORT;<\/code><\/pre>\n<p><strong>4.3 Monitoring<\/strong><\/p>\n<pre><code>SELECT name,\n       operation_type_desc,    -- ADD_CONSTRAINT\n       state_desc,\n       percent_complete\nFROM sys.index_resumable_operations\nWHERE operation_type_desc = 'ADD_CONSTRAINT';<\/code><\/pre>\n<p><strong>5\u00a0\u00b7\u00a0Database\u2011Scoped Enforcement<\/strong><\/p>\n<pre><code>ALTER DATABASE SCOPED CONFIGURATION\n    SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;   -- or FAIL_UNSUPPORTED<\/code><\/pre>\n<ul>\n<li><code>WHEN_SUPPORTED<\/code>\u00a0silently adds <code>RESUMABLE = ON<\/code>.<\/li>\n<li><code>FAIL_UNSUPPORTED<\/code>\u00a0errors on non\u2011resumable DDL.<\/li>\n<\/ul>\n<p><strong>6\u00a0\u00b7\u00a0Best\u2011Practice Checklist<\/strong><\/p>\n<ul>\n<li>Always set <code>MAX_DURATION<\/code> to define maintenance windows.<\/li>\n<li>Monitor DMVs and alert on unexpected <code>PAUSED<\/code> states.<\/li>\n<li>Adjust <code>MAXDOP<\/code> on resume to limit CPU impact.<\/li>\n<li>Ensure sufficient tempdb and log space for batch processing.<\/li>\n<li>Use <code>ABORT<\/code> only when discarding all progress is acceptable.<\/li>\n<li>Enable <code>ELEVATE_RESUMABLE<\/code> to enforce resumability across scripts.<\/li>\n<\/ul>\n<h3>Conclusion<\/h3>\n<p>Resumable online index rebuilds, resumable online index creation, and resumable <code>ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT<\/code> convert all\u2011or\u2011nothing maintenance into interruption\u2011tolerant processes. By batching work, persisting metadata, and exposing pause\/resume controls, SQL\u00a0Server minimizes log growth, shortens outage windows, and allows safe recovery after interruptions. Implement these features with proper DMV monitoring and database\u2011scoped enforcement to achieve reliable, low\u2011impact schema maintenance on large production systems.<\/p>\n<p>Further reading:<\/p>\n<ul>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/indexes\/guidelines-for-online-index-operations?view=sql-server-ver17\">Guidelines for Online Index Operations<\/a><\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/resumable-add-table-constraints?view=sql-server-ver17\">Resumable add table constraints<\/a><\/li>\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/indexes\/perform-index-operations-online?view=sql-server-ver17\">Perform Index Operations Online<\/a><\/li>\n<\/ul>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[104,54,40],"tags":[614,617,615,616,613,612,611,131],"class_list":["post-1162","post","type-post","status-publish","format-standard","hentry","category-indexing","category-maintenance","category-sql-server-2022","tag-add-constraint","tag-elevate_resumable","tag-enterprise-edition","tag-maintenance-automation","tag-online-index-create","tag-online-index-rebuild","tag-resumable-index","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).\" \/>\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.sqltabletalk.com\/?p=1162\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=1162\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-30T14:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Stephen Planck\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Stephen Planck\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162\"},\"author\":{\"name\":\"Stephen Planck\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"headline\":\"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT\",\"datePublished\":\"2025-07-30T14:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162\"},\"wordCount\":420,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"Add Constraint\",\"ELEVATE_RESUMABLE\",\"Enterprise Edition\",\"Maintenance Automation\",\"Online Index Create\",\"Online Index Rebuild\",\"Resumable Index\",\"SQL Server\"],\"articleSection\":[\"Indexing\",\"Maintenance\",\"SQL Server 2022\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1162#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=1162\",\"name\":\"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2025-07-30T14:00:00+00:00\",\"description\":\"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1162\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1162#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"],\"url\":\"https:\/\/www.sqltabletalk.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk","description":"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).","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.sqltabletalk.com\/?p=1162","og_locale":"en_US","og_type":"article","og_title":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk","og_description":"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).","og_url":"https:\/\/www.sqltabletalk.com\/?p=1162","og_site_name":"SQL Table Talk","article_published_time":"2025-07-30T14:00:00+00:00","author":"Stephen Planck","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Stephen Planck","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=1162#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1162"},"author":{"name":"Stephen Planck","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"headline":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT","datePublished":"2025-07-30T14:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1162"},"wordCount":420,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["Add Constraint","ELEVATE_RESUMABLE","Enterprise Edition","Maintenance Automation","Online Index Create","Online Index Rebuild","Resumable Index","SQL Server"],"articleSection":["Indexing","Maintenance","SQL Server 2022"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=1162#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=1162","url":"https:\/\/www.sqltabletalk.com\/?p=1162","name":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2025-07-30T14:00:00+00:00","description":"Large tables make routine maintenance risky. Traditional online index builds and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT operations run as a single long\u2011lived transaction. If an outage or fail\u2011over occurs near the end, SQL\u00a0Server rolls back everything, wasting time, expanding the transaction log, and extending maintenance windows. Beginning with SQL\u00a0Server\u00a02017, Microsoft introduced resumable maintenance so work can pause safely and continue later without losing progress. This post focuses on resumable online index creation and rebuild (2017\u20132022) and resumable online ADD\u00a0CONSTRAINT(2022).","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1162#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=1162"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=1162#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Resumable Maintenance in SQL\u00a0Server: Index Rebuilds, Index Creation, and ALTER\u00a0TABLE\u00a0ADD\u00a0CONSTRAINT"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"],"url":"https:\/\/www.sqltabletalk.com\/?author=1"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1162"}],"version-history":[{"count":4,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1162\/revisions"}],"predecessor-version":[{"id":1166,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1162\/revisions\/1166"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}