{"id":5058,"date":"2020-01-06T12:21:20","date_gmt":"2020-01-06T20:21:20","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=5058"},"modified":"2020-01-07T12:26:36","modified_gmt":"2020-01-07T20:26:36","slug":"the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/","title":{"rendered":"The Curious Case of\u2026 a truncated log file after a revert from snapshot"},"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 last week from someone who was very concerned to see a truncated log file after a revert from snapshot. They thought they&#8217;d come across a SQL Server bug and wanted to know if I&#8217;d seen this before.<\/p>\n<p style=\"text-align: justify;\">Well, the answer is &#8216;yes&#8217;, because that&#8217;s how SQL Server has behaved since SQL Server 2005 introduced database snapshots. Whenever you perform a revert to snapshot, the log file is discarded and a new one created with two 256KB VLFs.<\/p>\n<p style=\"text-align: justify;\">This behavior is by design, but understandably surprises people. The idea is that the log does have to be reset during a revert, because the database is being wound backwards in time physically, rather than by rolling back transactions. You&#8217;d think that SQL Server would recreate the log at the size it was previously, but the thinking was (when we built the feature) that if the previous log size was very large, recreating it would potentially take a long time, as the log must be zero-initialized on first allocation in the file system. So, it just whacks the log down to two small VLFs, which means after a revert you&#8217;ll need to manually grow the log again.<\/p>\n<p style=\"text-align: justify;\">Here&#8217;s an example using SQL Server 2019:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\n\r\nIF DATABASEPROPERTYEX (N'SalesDB_Snapshot', N'Version') &gt; 0\r\n\tDROP DATABASE &#x5B;SalesDB_Snapshot];\r\nGO\r\n\r\nRESTORE DATABASE SalesDB\r\n\tFROM DISK = N'D:\\SQLskills\\DemoBackups\\SalesDB2014.bak'\r\nWITH STATS = 10, REPLACE;\r\nGO\r\n\r\n-- Create the snapshot\r\nCREATE DATABASE &#x5B;SalesDB_Snapshot]\r\nON (\r\n\tNAME = N'SalesDBData',\r\n\tFILENAME = N'C:\\SQLskills\\SalesDBData.mdfss')\r\nAS SNAPSHOT OF &#x5B;SalesDB];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">Now, looking at the log file size (using the old DBCC commands instead of the new DMVs so people playing with this on older versions can follow along) and some of the fields removed from the results for clarity:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDBCC LOGINFO (N'SalesDB');\r\nDBCC SQLPERF (LOGSPACE);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nRecoveryUnitId FileId      FileSize             \r\n-------------- ----------- -------------------- \r\n0              2           10420224             \r\n0              2           10420224             \r\n0              2           10420224             \r\n0              2           10674176             \r\n\r\nDatabase Name Log Size (MB) Log Space Used (%) Status\r\n------------- ------------- ------------------ -----------\r\nmaster        1.992188      43.77451           0\r\ntempdb        7.992188      6.042278           0\r\nmodel         19.61719      6.586021           0\r\nSalesDB       39.99219      18.78174           0\r\n<\/pre>\n<p style=\"text-align: justify;\">Four VLFs of about 10MB each in the <em>SalesDB<\/em> database. Now I&#8217;ll revert to the snapshot and check again:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nRESTORE DATABASE &#x5B;SalesDB]\r\nFROM DATABASE_SNAPSHOT = N'SalesDB_Snapshot';\r\nGO\r\n\r\nDBCC LOGINFO (N'SalesDB');\r\nDBCC SQLPERF (LOGSPACE);\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nRecoveryUnitId FileId      FileSize             \r\n-------------- ----------- -------------------- \r\n0              2           253952\r\n0              2           253952           \r\n\r\nDatabase Name Log Size (MB) Log Space Used (%) Status\r\n------------- ------------- ------------------ -----------\r\nmaster        1.992188      43.77451           0\r\ntempdb        7.992188      6.042278           0\r\nmodel         19.61719      6.586021           0\r\nSalesDB       0.484375      60.28226           0\r\n<\/pre>\n<p style=\"text-align: justify;\">You can see that the log has been reset to two 256KB VLFs.<\/p>\n<p style=\"text-align: justify;\">Bottom line: it&#8217;s expected behavior you need to be aware of. It&#8217;s also a neat way to reset the VLFs in a log if someone created the initial log to be enormous and you want to resize it to be a lot smaller, as there&#8217;s no other way of reducing the size of the first couple of VLFs.<\/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-5058","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 a truncated log file after a revert from snapshot - 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-a-truncated-log-file-after-a-revert-from-snapshot\/\" \/>\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 a truncated log file after a revert from snapshot - 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-a-truncated-log-file-after-a-revert-from-snapshot\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-06T20:21:20+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-07T20:26:36+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=\"3 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-a-truncated-log-file-after-a-revert-from-snapshot\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/\",\"name\":\"The Curious Case of\u2026 a truncated log file after a revert from snapshot - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2020-01-06T20:21:20+00:00\",\"dateModified\":\"2020-01-07T20:26:36+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-a-truncated-log-file-after-a-revert-from-snapshot\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/#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 a truncated log file after a revert from snapshot\"}]},{\"@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 a truncated log file after a revert from snapshot - 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-a-truncated-log-file-after-a-revert-from-snapshot\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of\u2026 a truncated log file after a revert from snapshot - 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-a-truncated-log-file-after-a-revert-from-snapshot\/","og_site_name":"Paul S. Randal","article_published_time":"2020-01-06T20:21:20+00:00","article_modified_time":"2020-01-07T20:26:36+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/","name":"The Curious Case of\u2026 a truncated log file after a revert from snapshot - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2020-01-06T20:21:20+00:00","dateModified":"2020-01-07T20:26:36+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-a-truncated-log-file-after-a-revert-from-snapshot\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-a-truncated-log-file-after-a-revert-from-snapshot\/#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 a truncated log file after a revert from snapshot"}]},{"@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\/5058","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=5058"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5058\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5058"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}