{"id":5232,"date":"2022-03-09T13:35:23","date_gmt":"2022-03-09T21:35:23","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/paul\/?p=5232"},"modified":"2022-03-09T13:40:15","modified_gmt":"2022-03-09T21:40:15","slug":"the-curious-case-of-random-failing-log-shipping-restores","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/","title":{"rendered":"The Curious Case of\u2026 random failing log shipping restores"},"content":{"rendered":"

(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0newsletter<\/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

A quick one this time… Jonathan was working with a client recently who was experiencing weird, random failures on a log shipping secondary server. The log backups were set to restore and put the database in STANDBY mode and the option ‘Disconnect users in the database when restoring backups’ was enabled. Every so often the restore process was failing with the error ‘Exclusive access could not be obtained’ – how could that happen?<\/p>\n

Jonathan was able to reproduce the failures in our test lab using a constant, high rate of connection requests to the database. It turns out that the log shipping executable sets the database to SINGLE_USER mode to kick out any connections and then starts the restore, but as two separate batches. This means there’s a tiny race condition where another connection can be made to the database which will prevent the restore from being able to run, so with a high rate of connection requests, it’s only a matter of time until the race condition is met.<\/p>\n

It’s unclear whether any changes will be made to the way log shipping works, but in the meantime, this is a scenario to be aware of.<\/p>\n","protected":false},"excerpt":{"rendered":"

(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.) A quick one […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58,115],"tags":[],"class_list":["post-5232","post","type-post","status-publish","format-standard","hentry","category-log-shipping","category-the-curious-case-of"],"yoast_head":"\nThe Curious Case of\u2026 random failing log shipping restores - 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-random-failing-log-shipping-restores\/\" \/>\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 random failing log shipping restores - 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.) A quick one […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2022-03-09T21:35:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-03-09T21:40:15+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-random-failing-log-shipping-restores\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/\",\"name\":\"The Curious Case of\u2026 random failing log shipping restores - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2022-03-09T21:35:23+00:00\",\"dateModified\":\"2022-03-09T21:40:15+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-random-failing-log-shipping-restores\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/#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 random failing log shipping restores\"}]},{\"@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 random failing log shipping restores - 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-random-failing-log-shipping-restores\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of\u2026 random failing log shipping restores - 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.) A quick one […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/","og_site_name":"Paul S. Randal","article_published_time":"2022-03-09T21:35:23+00:00","article_modified_time":"2022-03-09T21:40:15+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-random-failing-log-shipping-restores\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/","name":"The Curious Case of\u2026 random failing log shipping restores - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2022-03-09T21:35:23+00:00","dateModified":"2022-03-09T21:40:15+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-random-failing-log-shipping-restores\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-random-failing-log-shipping-restores\/#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 random failing log shipping restores"}]},{"@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\/5232","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=5232"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5232\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5232"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5232"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5232"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}