{"id":4878,"date":"2018-03-21T16:48:35","date_gmt":"2018-03-21T23:48:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4878"},"modified":"2018-03-21T16:48:35","modified_gmt":"2018-03-21T23:48:35","slug":"sqlskills-sql101-is-the-recovery-interval-guaranteed","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/","title":{"rendered":"SQLskills SQL101: Is the recovery interval guaranteed?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">One of the concepts I find people misunderstand frequently is the <em>recovery interval<\/em>, either for the server as a whole or the per-database setting that was introduced in SQL Server 2012 for indirect checkpoints.<\/p>\n<p style=\"text-align: justify;\">There are two misconceptions here:<\/p>\n<ol style=\"text-align: justify;\">\n<li>The recovery interval equals how often a checkpoint will occur<\/li>\n<li>SQL Server guarantees the recovery interval (i.e. crash recovery for the database will only take the amount of time specified in the recovery interval)<\/li>\n<\/ol>\n<p style=\"text-align: justify;\">A bit of background: crash recovery has two tasks to perform: replaying log records from committed transactions (called REDO) and removing the effect of log records from uncommitted transactions (called UNDO). REDO only has to occur if there have been committed transactions where the changed data pages have not been written to disk (which is done by periodic checkpoints or a lazy writer background thread if there\u2019s memory pressure on the buffer pool).<\/p>\n<p style=\"text-align: justify;\">The recovery interval specifies an ideal upper bound on how long the REDO portion of crash recovery will take. The length of time REDO takes depends on how many log records need to be replayed from committed transactions. To help REDO stay on track, the recovery interval setting forces a checkpoint to occur when a certain number of log records have been generated (a number calculated using the chosen recovery interval).<\/p>\n<p style=\"text-align: justify;\">So although recovery interval *does* control how often a checkpoint occurs, the recovery interval time does not equal the time between checkpoints.<\/p>\n<p style=\"text-align: justify;\">For instance, if the recovery interval is set to one minute (the default), and a checkpoint occurs *now*, when will the next checkpoint occur? If activity in the database is very infrequent, a checkpoint may not occur for a very long time because the calculated recovery time for that activity will be less than one minute. If activity in the database is extremely high, checkpoints may occur every few seconds. Checkpoint frequency entirely depends on how fast log records are being generated *and* the recovery interval setting.<\/p>\n<p style=\"text-align: justify;\">And of course SQL Server cannot *guarantee* the recovery interval. Imagine the recovery interval is set to one minute, and I start a transaction that updates millions of records over several hours. If SQL Server crashes just before the transaction commits, how long do you think it will take to roll back the millions of updates? Certainly it will take much longer than one minute \u2013 the configured recovery interval.<\/p>\n<p style=\"text-align: justify;\">So I can update my definition to be: the recovery interval is therefore the ideal upper bound on how long the REDO portion of crash recovery will take, assuming no long-running transactions.<\/p>\n<p style=\"text-align: justify;\">Hope this helps clear up any misconceptions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One of the concepts I find people [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[108,98],"tags":[],"class_list":["post-4878","post","type-post","status-publish","format-standard","hentry","category-sql101","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Is the recovery interval guaranteed? - 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\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Is the recovery interval guaranteed? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One of the concepts I find people [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-21T23:48:35+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\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/\",\"name\":\"SQLskills SQL101: Is the recovery interval guaranteed? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2018-03-21T23:48:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Is the recovery interval guaranteed?\"}]},{\"@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":"SQLskills SQL101: Is the recovery interval guaranteed? - 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\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Is the recovery interval guaranteed? - Paul S. Randal","og_description":"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. One of the concepts I find people [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/","og_site_name":"Paul S. Randal","article_published_time":"2018-03-21T23:48:35+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\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/","name":"SQLskills SQL101: Is the recovery interval guaranteed? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2018-03-21T23:48:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-is-the-recovery-interval-guaranteed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Is the recovery interval guaranteed?"}]},{"@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\/4878","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=4878"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4878\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}