{"id":5009,"date":"2019-09-04T13:57:21","date_gmt":"2019-09-04T20:57:21","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=5009"},"modified":"2019-09-04T13:57:21","modified_gmt":"2019-09-04T20:57:21","slug":"the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/","title":{"rendered":"The Curious Case of\u2026 too few log records for an offline index rebuild"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0<a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n<p style=\"text-align: justify;\">I had an email question today asking why there were too few log records for an offline index rebuild the person was doing. They were concerned that the index they were rebuilding in the full recovery model wasn&#8217;t being rebuilt properly because instead of seeing at least one log record per row, they were seeing far fewer.<\/p>\n<p style=\"text-align: justify;\">There&#8217;s a very simple answer: SQL Server will always try to generate as little log volume as possible.<\/p>\n<p style=\"text-align: justify;\">Let&#8217;s say you have an index with 100,000 rows, and 100 rows fit on each index leaf page. When doing an offline rebuild in the full recovery model, SQL Server will not log an <em>LOP_INSERT_ROWS<\/em> log record for each index row, as that&#8217;s too inefficient, given the 100+ bytes of overhead in each log record. Instead it will log about 1,000 <em>LOP_FORMAT_PAGE<\/em> log records, where each log record contains a full page image with 100 rows in it. That saves the overhead of 99 log records for each 100 rows inserted into the new index &#8211; for a total saving of 99,900 x log-record-overhead in log volume (as it would still have to log the <em>LOP_FORMAT_PAGE<\/em> log records for the page header of each of the 1,000 pages created).<\/p>\n<p style=\"text-align: justify;\">And if you think about it carefully, that approach gives just the same ability to recover the transaction in the event of a crash, as the net effect of each approach is the same.<\/p>\n<p style=\"text-align: justify;\">SQL Server does as much as it can to limit what gets logged for efficiency, which is a good thing if you think of all the places that log is used (including backups, restores, log shipping, replication, mirroring, AGs).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) I had an [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[115,98],"tags":[],"class_list":["post-5009","post","type-post","status-publish","format-standard","hentry","category-the-curious-case-of","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Curious Case of\u2026 too few log records for an offline index rebuild - 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\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of\u2026 too few log records for an offline index rebuild - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) I had an [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-04T20:57:21+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=\"2 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\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/\",\"name\":\"The Curious Case of\u2026 too few log records for an offline index rebuild - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2019-09-04T20:57:21+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of\u2026 too few log records for an offline index rebuild\"}]},{\"@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":"The Curious Case of\u2026 too few log records for an offline index rebuild - 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\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of\u2026 too few log records for an offline index rebuild - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) I had an [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/","og_site_name":"Paul S. Randal","article_published_time":"2019-09-04T20:57:21+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/","name":"The Curious Case of\u2026 too few log records for an offline index rebuild - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2019-09-04T20:57:21+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-too-few-log-records-for-an-offline-index-rebuild\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of\u2026 too few log records for an offline index rebuild"}]},{"@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\/5009","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=5009"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5009\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}