{"id":15804,"date":"2021-02-25T14:52:21","date_gmt":"2021-02-25T13:52:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/"},"modified":"2025-10-01T11:50:31","modified_gmt":"2025-10-01T09:50:31","slug":"sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/","title":{"rendered":"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild"},"content":{"rendered":"

Introduction<\/h3>\n

In this blog post, I will demonstrate how the Resumable<\/a> capability of Online index rebuild operation can help you to keep the transaction log file size under control.<\/p>\n

An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the transaction log file can grow until your run out of disk space.
\nOn failure, the transaction needs to rollback. You end up with a large transaction log file, no free space on your transaction log file volume, and an index not rebuilt.<\/p>\n

Since SQL Server 2017 with Enterprise Edition, using the Resumable option of index online rebuild operation we can try to keep under control the transaction log file size.<\/p>\n

Demo<\/h3>\n

For the demo, I’ll use the AdventureWorks<\/a> database with the Adam Machanic’s bigAdventures<\/a> tables.<\/p>\n

Index rebuild Log usage<\/h4>\n

My transaction log file size is 1 GB and it’s empty.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n<\/td>\n
\n
\n
USE [AdventureWorks2019]<\/code><\/div>\n
go<\/code><\/div>\n
select<\/code> total_log_size_in_bytes\/1024\/1024 <\/code>AS<\/code> TotalLogSizeMB<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>, (total_log_size_in_bytes - used_log_space_in_bytes)\/1024\/1024 <\/code>AS<\/code> FreeSpaceMB<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>, used_log_space_in_bytes\/1024.\/1024\u00a0 <\/code>as<\/code> UsedLogSpaceMB,<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>used_log_space_in_percent<\/code><\/div>\n
from<\/code> sys.dm_db_log_space_usage;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I now rebuild the index on bigTransactionHistory.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
ALTER<\/code> INDEX<\/code> IX_ProductId_TransactionDate <\/code>ON<\/code> bigTransactionHistory REBUILD<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>WITH<\/code> (ONLINE=<\/code>ON<\/code>);<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

\"\"<\/a>
\nI had a few autogrowth events bringing my file to 3583 MB. The log space required to rebuild this index is about 3500 MB.<\/p>\n

Now, let’s say I want to limit my transaction log file to 2 GB.<\/p>\n

Index rebuild script<\/h4>\n

First, I build a table that contains the list of indexes I have to rebuild during my maintenance window. For the demo purpose it’s a very simple one:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
select<\/code> *<\/code><\/div>\n
from<\/code> IndexToMaintain;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

\"\"<\/p>\n

The idea is to go through all the indexes to rebuild and start a Rebuild with the option RESUMABLE=ON.
\nWhen a rebuild is done the value for the RebuildStatus column is updated to 1.<\/p>\n

Here is the code:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n<\/td>\n
\n
\n
WHILE (<\/code>select<\/code> Count<\/code>(*) <\/code>from<\/code> IndexToMaintain <\/code>where<\/code> RebuildStatus = 0) > 0<\/code><\/div>\n
BEGIN<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>DECLARE<\/code> @rebuild <\/code>varchar<\/code>(1000)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @DatabaseName <\/code>varchar<\/code>(1000)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @TableName <\/code>varchar<\/code>(1000)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @IndexName <\/code>varchar<\/code>(1000)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @id <\/code>int<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>select<\/code> @DatabaseName = DatabaseName<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @TableName = TableName<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @IndexName = IndexName<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>, @id = id<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>from<\/code> IndexToMaintain <\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>where<\/code> RebuildStatus = 0;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>SET<\/code> @rebuild = CONCAT(<\/code>'ALTER INDEX '<\/code>, @IndexName, <\/code>' ON '<\/code>,@DatabaseName, <\/code>'.dbo.'<\/code>, @TableName, <\/code>' REBUILD WITH (ONLINE=ON, RESUMABLE=ON);'<\/code>)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>exec<\/code>(@rebuild)<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>UPDATE<\/code> IndexToMaintain <\/code>SET<\/code> RebuildStatus = 1 <\/code>where<\/code> id = @id;<\/code><\/div>\n
END<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The commands executed will look like this.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
ALTER<\/code> INDEX<\/code> IX_ProductId_TransactionDate <\/code>ON<\/code> bigTransactionHistory REBUILD<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>WITH<\/code> (ONLINE=<\/code>ON<\/code>, RESUMABLE=<\/code>ON<\/code>);<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The Job is scheduled to be run at a “high” frequency (depending on the file size) during the defined maintenance window. For example, it could be every 5 minutes between 1am and 3am.\"\"<\/a><\/p>\n

We don’t need to use ALTER INDEX with RESUME to resume an index rebuild, we can just execute the original ALTER INDEX command again, as found in the DMV. It’s very useful and simplifies this kind of script.<\/p>\n

Alert on Log Space usage<\/h4>\n

To contain the transaction log file size I create an Agent Alert that will be triggered when the file is used at 50%.\"\"<\/a>In response to this Alert, it will execute another Job with 2 steps.<\/p>\n

The first one checks the DMV index_resumable_operations for any running resumable index operation and pauses it.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n<\/td>\n
\n
\n
IF EXISTS (<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>select<\/code> *<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>from<\/code> AdventureWorks2019.sys.index_resumable_operations<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>where<\/code> state_desc = <\/code>'RUNNING'<\/code><\/div>\n
)<\/code><\/div>\n
BEGIN<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>DECLARE<\/code> @sqlcmd <\/code>varchar<\/code>(1000)\u00a0\u00a0 <\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>select<\/code> @sqlcmd=CONCAT(<\/code>'ALTER INDEX '<\/code>, iro.<\/code>name<\/code>, <\/code>' ON '<\/code>, OBJECT_NAME(o.object_id), <\/code>' PAUSE;'<\/code>)<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>from<\/code> AdventureWorks2019.sys.index_resumable_operations <\/code>AS<\/code> iro<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>join<\/code> sys.objects <\/code>AS<\/code> o<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>on<\/code> iro.object_id = o.object_id<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>where<\/code> iro.state_desc = <\/code>'RUNNING'<\/code>;<\/code><\/div>\n
<\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>EXEC<\/code>(@sqlcmd)<\/code><\/div>\n
END<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The second step will then perform a Log backup to free up the transaction log space inside the file.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
DECLARE<\/code> @backupFile <\/code>varchar<\/code>(1000) <\/code><\/div>\n
SET<\/code> @backupFile = <\/code>'C:\\Backup\\AdventureWorks2019_'<\/code>+<\/code>replace<\/code>(<\/code>convert<\/code>(<\/code>varchar<\/code>(20),GetDate(),120), <\/code>':'<\/code>, <\/code>'_'<\/code>)+<\/code>'.trn'<\/code><\/div>\n
BACKUP LOG AdventureWorks2019 <\/code>TO<\/code> DISK = @backupFile<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The command to be executed by this Job:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
ALTER<\/code> INDEX<\/code> IX_ProductId_TransactionDate <\/code>ON<\/code> bigTransactionHistory PAUSE;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Running the Rebuild<\/h3>\n

I set the RebuildStatus value for my index at 0 and enable the Job (scheduled to run every minute). It starts to run at 13:04.
\nAs we can see in the Job history the index rebuild job ran twice (around 23s) with a failed status. This means that during rebuild it was stopped by the other job doing a PAUSE followed by a log backup.
\nThe third time it runs it could finish rebuilding the index, set the RebuildStatus to 1, and quit successfully.\"\"<\/a>The Job triggered by the alert has been run twice.\"\"<\/a>Two transaction log backups have been performed.\"\"<\/a>While doing the rebuild we managed to keep the transaction log file at a 2GB size compared to the 3.5GB it would use without using the Resumable feature.\"\"<\/a><\/p>\n

Conclusion<\/h3>\n

This demo was just an example of how the resumable option of index rebuild could be used to contain the transaction file size during index maintenance.
\nObviously, this solution is not usable as-is for production. You will find the code on my GitHub<\/a> if you want to play with it.
\nI hope you found this blog interesting. Feel free to give me feedback in the comments below.<\/p>\n

Written by Steven Naudet<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the […]<\/p>\n","protected":false},"author":26,"featured_media":15813,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,99],"tags":[2259,2550,533],"type_dbi":[],"class_list":["post-15804","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","category-sql-server","tag-resumable","tag-sql-server-2","tag-transaction-log"],"acf":[],"yoast_head":"\nSQL Server: Control the size of your Transaction Log file with Resumable 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-control-the-size-of-your-transaction-log-file-with-resumable-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: Control the size of your Transaction Log file with Resumable Index Rebuild\" \/>\n<meta property=\"og:description\" content=\"Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-02-25T13:52:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-01T09:50:31+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png\" \/>\n\t<meta property=\"og:image:width\" content=\"602\" \/>\n\t<meta property=\"og:image:height\" content=\"50\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\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\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild\",\"datePublished\":\"2021-02-25T13:52:21+00:00\",\"dateModified\":\"2025-10-01T09:50:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\"},\"wordCount\":647,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png\",\"keywords\":[\"Resumable\",\"SQL Server\",\"transaction log\"],\"articleSection\":[\"Database Administration & Monitoring\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\",\"name\":\"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png\",\"datePublished\":\"2021-02-25T13:52:21+00:00\",\"dateModified\":\"2025-10-01T09:50:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png\",\"width\":602,\"height\":50},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Control the size of your Transaction Log file with Resumable 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\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server: Control the size of your Transaction Log file with Resumable 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-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild","og_description":"Introduction In this blog post, I will demonstrate how the Resumable capability of Online index rebuild operation can help you to keep the transaction log file size under control. An index rebuild operation is done in a single transaction that can require a significant log space. When doing a Rebuild on a large index the […]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/","og_site_name":"dbi Blog","article_published_time":"2021-02-25T13:52:21+00:00","article_modified_time":"2025-10-01T09:50:31+00:00","og_image":[{"width":602,"height":50,"url":"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png","type":"image\/png"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild","datePublished":"2021-02-25T13:52:21+00:00","dateModified":"2025-10-01T09:50:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/"},"wordCount":647,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png","keywords":["Resumable","SQL Server","transaction log"],"articleSection":["Database Administration & Monitoring","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/","name":"SQL Server: Control the size of your Transaction Log file with Resumable Index Rebuild - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png","datePublished":"2021-02-25T13:52:21+00:00","dateModified":"2025-10-01T09:50:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Blog15_1_TXLogUsage.png","width":602,"height":50},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-control-the-size-of-your-transaction-log-file-with-resumable-index-rebuild\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Control the size of your Transaction Log file with Resumable 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\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15804","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\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15804"}],"version-history":[{"count":2,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15804\/revisions"}],"predecessor-version":[{"id":40601,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15804\/revisions\/40601"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/15813"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15804"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}