{"id":891,"date":"2009-04-21T17:48:00","date_gmt":"2009-04-21T17:48:00","guid":{"rendered":"\/blogs\/paul\/post\/Why-could-restoring-a-log-shipping-log-backup-be-slow.aspx"},"modified":"2009-04-21T17:48:00","modified_gmt":"2009-04-21T17:48:00","slug":"why-could-restoring-a-log-shipping-log-backup-be-slow","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/","title":{"rendered":"Why could restoring a log-shipping log backup be slow?"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">A short post to start the day (in India) and then at lunchtime I&#39;ll do how it works: <font face=\"courier new,courier\">FILESTREAM<\/font> garbage collection.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\"><font size=\"2\">This is a question that came up recently on an MVP discussion list: <em>why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary?<\/em><\/font><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">First answer I thought of was that the I\/O subsystem on the log-shipping secondary was very slow, or broken in some way, so I\/Os were taking ages to complete. Nope &#8211; I\/O subsystem was performing well.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Next thing I suggested was the the log-shipping secondary was restoring the log backups using <font face=\"courier new,courier\">WITH STANDBY<\/font> and there was something like an index rebuild that was being rolled back. As an aside, when you restore log backups on the log-shipping secondary, you have a choice how they recovery: either <font face=\"courier new,courier\">WITH NORECOVERY<\/font> or <font face=\"courier new,courier\">WITH STANDBY<\/font>. The first option doesn&#39;t allow any access to the database, as the database is still &quot;in recovery&quot;. The second option runs the REDO part of recovery, then runs the UNDO part of recovery, but saves the details of what it had to do for UNDO into a file (who&#39;s name and location you specify). It then allows read-only access to the database, for queries\/reporting\/whatever. Obviously if there&#39;s a lot of transaction log that has to be undone (i.e. rolling back transactions that weren&#39;t committed at the time the log backup completed), then this could take some time. But 40 minutes? No. That wasn&#39;t it.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">I was partly right on my second guess. The *previous* log backup that was restored <font face=\"courier new,courier\">WITH STANDBY<\/font> contained a long-running index operation, and so the undo file that the restore created was *huge*. The next log backup that&#39;s restored after a <font face=\"courier new,courier\">RESTORE &#8230; WITH STANDBY<\/font>, must first undo everything in the undo file (i.e. put the database back to the exact state it was in as if the <font face=\"courier new,courier\">WITH STANDBY<\/font> part of the restore never happened) before it can restore more transaction log. In this case, no-one had noticed that the previous log restore *also* took a lot of time and created the huge undo file. It just looked like the 200MB log backup was causing the problem.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">My advice: in any kind of there&#39;s-suddenly-a-corruption-or-performance-problem situation, don&#39;t just look at what&#39;s happening now. Always look at what happened leading up to the problem, as the answer is often there.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A short post to start the day (in India) and then at lunchtime I&#39;ll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,31,46,58,66,98],"tags":[],"class_list":["post-891","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-database-maintenance","category-high-availability","category-log-shipping","category-performance-tuning","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Why could restoring a log-shipping log backup be slow? - 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\/why-could-restoring-a-log-shipping-log-backup-be-slow\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why could restoring a log-shipping log backup be slow? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A short post to start the day (in India) and then at lunchtime I&#039;ll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-04-21T17:48:00+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\/why-could-restoring-a-log-shipping-log-backup-be-slow\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/\",\"name\":\"Why could restoring a log-shipping log backup be slow? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-04-21T17:48:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Why could restoring a log-shipping log backup be slow?\"}]},{\"@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":"Why could restoring a log-shipping log backup be slow? - 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\/why-could-restoring-a-log-shipping-log-backup-be-slow\/","og_locale":"en_US","og_type":"article","og_title":"Why could restoring a log-shipping log backup be slow? - Paul S. Randal","og_description":"A short post to start the day (in India) and then at lunchtime I&#39;ll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/","og_site_name":"Paul S. Randal","article_published_time":"2009-04-21T17:48:00+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\/why-could-restoring-a-log-shipping-log-backup-be-slow\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/","name":"Why could restoring a log-shipping log backup be slow? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-04-21T17:48:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-could-restoring-a-log-shipping-log-backup-be-slow\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Why could restoring a log-shipping log backup be slow?"}]},{"@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\/891","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=891"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/891\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}