{"id":1835,"date":"2014-01-16T11:00:30","date_gmt":"2014-01-16T16:00:30","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1835"},"modified":"2014-11-13T13:45:48","modified_gmt":"2014-11-13T18:45:48","slug":"log-reuse-waits-explained-active_backup_or_restore","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/","title":{"rendered":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThere are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: ACTIVE_BACKUP_OR_RESTORE<\/span>\n<\/p>\n

Backup and Restore Consistency<\/h3>\n

\nSQL Server is first and foremost an ACID<\/a> compliant database management system. That means that the rules dictated by this set of properties are adhered to under all circumstances. The ACID acronym stands for Atomicity, Consistency, Isolation and Durability. Without going into any detail, they guarantee among other things that a transaction that was committed does not suddenly disappear or worse partially disappear. In the context of backups and restores that means that after I restore from a backup all the data within that database must also be transactionally consistent.\n<\/p>\n

\nOne way to achieve backup consistency is to take the database offline before each backup and bring it online afterwards. That way no concurrent changes to the data can happen that would only be partially captured by the backup. However that is clearly not a desirable solution as, depending on the size of the database, a backup might take a considerable amount of time.\n<\/p>\n

\nAnother way is to record all transactions that are happening during the backup and then run a standard crash recovery using this data after every restore. This is the approach SQL Server takes. All transaction log records of transactions that were active at any time during the backup operation are included in the backup at the end of its run. That way SQL Server has enough information to undo the changes of transactions that were still active at the end of the backup and redo the changes of transactions that were committed during the backup.\n<\/p>\n

Backup Wait<\/h3>\n

\nBecause the log records are captured at the end of the backup operation, the virtual log files containing transaction log records of transactions that were active at any time during the backup cannot be reused until that last phase of the backup process has finished.\n<\/p>\n

Restore Wait<\/h3>\n

\nAt the end of any restore, SQL Server has to run crash recovery on the restored data. That means that during that time log reuse has to be prevented too. In most cases a database won't be online during its restore and this will therefore not be a problem. However, if you did a partial restore of only the active file groups of your database, you can bring those online before starting the restore of that large file group with the archive data. During that restore you will also not be able to reuse your log.\n<\/p>\n

Resolving the ACTIVE_BACKUP_OR_RESTORE Wait<\/h3>\n

\nIf SQL Server runs out of transaction log space during a backup or restore operation, the log_reuse_wait_desc<\/span> column for that database will return a value of ACTIVE_BACKUP_OR_RESTORE<\/span>.\n<\/p>\n

\nIn either case you have to wait for the operation to finish. That means if your backup strategy involves taking full or differential backups during a busy time, you have to give the transaction log enough room to grow. This is particularly important if the database is large, causing backup operations to potentially run for a long time.\n<\/p>\n

\nYou also have to account for transaction log growth if your restore strategy includes partial restores while other parts of the database are online.\n<\/p>\n

Summary<\/h3>\n

\nAt the end of each backup SQL Server captures the piece of the transaction log that covers all transactions that were active at any time during the backup. Because of that the virtual log files containing those transaction log records can't be reused during a backup. SQL Server will return a log_reuse_wait_desc<\/span> value of ACTIVE_BACKUP_OR_RESTORE<\/span> if it runs out of virtual log files during that time. During a restore operation the log cannot be reused either as the log records are required for the crash recovery process that is part of each restore operation. This is true too for database that are partially online during a restore operation.\n<\/p>\n

Log Reuse Wait Series<\/h3>\n

\nThis post is part of a short series about circumstances that prevent transaction log truncation.
\nBelow is a list of links to the posts that are already available.\n<\/p>\n

\n
\n

Eight Reasons why your Transaction Log Files keep growing<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: CHECKPOINT<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_BACKUP<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_TRANSACTION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_MIRRORING<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: REPLICATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_SCAN<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[69,5,61,27,62],"tags":[70,73,74,72,15,63,64],"class_list":["post-1835","post","type-post","status-publish","format-standard","hentry","category-backup","category-general","category-log-reuse-wait","category-series","category-transaction-log","tag-backup-2","tag-backup-plan","tag-backup-strategy","tag-log-truncation","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-16T16:00:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:45:48+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE\",\"datePublished\":\"2014-01-16T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:45:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/\"},\"wordCount\":777,\"commentCount\":0,\"keywords\":[\"backup\",\"backup plan\",\"backup strategy\",\"log truncation\",\"SQL Server\",\"transaction log\",\"transaction log reuse\"],\"articleSection\":[\"Backup\",\"General\",\"Log Reuse Wait\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/\",\"name\":\"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-16T16:00:30+00:00\",\"dateModified\":\"2014-11-13T18:45:48+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1835\\\/log-reuse-waits-explained-active_backup_or_restore\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net","description":"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.","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:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net","og_description":"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-16T16:00:30+00:00","article_modified_time":"2014-11-13T18:45:48+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE","datePublished":"2014-01-16T16:00:30+00:00","dateModified":"2014-11-13T18:45:48+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/"},"wordCount":777,"commentCount":0,"keywords":["backup","backup plan","backup strategy","log truncation","SQL Server","transaction log","transaction log reuse"],"articleSection":["Backup","General","Log Reuse Wait","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/","url":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/","name":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-16T16:00:30+00:00","dateModified":"2014-11-13T18:45:48+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting ACTIVE_BACKUP_OR_RESTORE as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1835\/log-reuse-waits-explained-active_backup_or_restore\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-tB","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1835"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1835\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}