{"id":1830,"date":"2014-01-15T11:00:22","date_gmt":"2014-01-15T16:00:22","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1830"},"modified":"2014-11-13T13:45:54","modified_gmt":"2014-11-13T18:45:54","slug":"log-reuse-waits-explained-log_backup","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/","title":{"rendered":"Log Reuse Waits Explained: LOG_BACKUP"},"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: LOG_BACKUP<\/span>\n<\/p>\n

Recovery Model<\/h3>\n

\nSQL Server knows three recovery models: Simple, full and bulk-logged. The main purpose is to influence how much data would be lost during a disaster. The recovery model it is a database wide setting.\n<\/p>\n

Simple<\/h3>\n

\nIf your database recovery model is set to simple, you can take full and differential backups. When disaster strikes your only option is to restore the last backup you had taken. Everything that happened in your database since then will be lost.\n<\/p>\n

\nYou can alleviate that by taking backups more frequent, but that can cause a higher strain on your resources.\n<\/p>\n

Full<\/h3>\n

\nIf you have set your database to recovery model full and you have taken at least one full or differential backup since, you have one more option. If the disaster for example affected the drive with the data files, but the log files are still readable, you can take a tail-log backup to capture all changes that happened after the last backup. With that you can restore right up to the point when the disaster started and you are not losing any committed data.\n<\/p>\n

\nHowever, this flexibility comes at a price. To be able to offer that backup restore capability SQL Server will not reuse any part of the transaction log, until you have taken a log backup of that part. So, to be able to reuse your virtual log files you need to regularly execute a log backup. A full or differential backup does not have any effect on log reuse.\n<\/p>\n

\nThere is an additional advantage to having this log backup chain. Because the log is written sequentially, you can now tell SQL Server to stop a restore process at any given point in time. With that you can restore your database to just before the accidental delete without where clause was executed.\n<\/p>\n

\nYou also have two independent backup channels. While you have to start every restore with a full backup, is does not need to be the most recent one if you still have a complete log backup chain since that backup. The restore will take longer this way, but if the most recent full backup was damaged this can get your data back.\n<\/p>\n

Bulk Logged<\/h3>\n

\nFor the purposes of this discussion the bulk logged recovery model is very similar to the full recovery model. The only difference is, that in this recovery model actual bulk operations like the BCP command prevent point in time restores to any time that is covered by a log backup containing the bulk operation fully or partially. Also, log backups might execute slower with this recovery model depending on the size of your database and the operations performed.\n<\/p>\n

Waiting for the Log Backup<\/h3>\n

\nAs I already mentioned above, if the database is in full recovery model, the virtual log files cannot be reused if they have not been backed up yet. If this is currently the case in a database, the log_reuse_wait_desc<\/span> column will report a value of LOG_BACKUP<\/span>.\n<\/p>\n

\nSolving this problem is simple: Take a log backup.\n<\/p>\n

\nIf the business does not require the point in time restore capability and can afford losing (depending on your backup schedule) a day's worth of data you can also switch the database to the simple recovery model. This might be appropriate for example for development databases that can be recreated.\n<\/p>\n

Summary<\/h3>\n

\nIf your database recovery model is \"full\" SQL Server cannot reuse virtual log files unless all contained transaction log records have been backed up with a log backup. If a log backup is outstanding the log has to grow to accommodate new data changes. During this time you will see a log_reuse_wait_desc<\/span> of LOG_BACKUP<\/span>. Full or differential backups do not backup all transaction log records, so you need to run an actual log backup to allow for virtual log file reuse.\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 LOG_BACKUP 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,71,15,63,64,17],"class_list":["post-1830","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-log-backup","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse","tag-transactions"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: LOG_BACKUP - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting LOG_BACKUP 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\/1830\/log-reuse-waits-explained-log_backup\/\" \/>\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: LOG_BACKUP - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting LOG_BACKUP 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\/1830\/log-reuse-waits-explained-log_backup\/\" \/>\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-15T16:00:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:45:54+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\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: LOG_BACKUP\",\"datePublished\":\"2014-01-15T16:00:22+00:00\",\"dateModified\":\"2014-11-13T18:45:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/\"},\"wordCount\":783,\"commentCount\":0,\"keywords\":[\"backup\",\"log backup\",\"SQL Server\",\"transaction log\",\"transaction log reuse\",\"Transactions\"],\"articleSection\":[\"Backup\",\"General\",\"Log Reuse Wait\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/\",\"name\":\"Log Reuse Waits Explained: LOG_BACKUP - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-15T16:00:22+00:00\",\"dateModified\":\"2014-11-13T18:45:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting LOG_BACKUP as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1830\\\/log-reuse-waits-explained-log_backup\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: LOG_BACKUP\"}]},{\"@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: LOG_BACKUP - sqlity.net","description":"Is your database log file growing out of bound and reporting LOG_BACKUP 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\/1830\/log-reuse-waits-explained-log_backup\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: LOG_BACKUP - sqlity.net","og_description":"Is your database log file growing out of bound and reporting LOG_BACKUP as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-15T16:00:22+00:00","article_modified_time":"2014-11-13T18:45:54+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\/1830\/log-reuse-waits-explained-log_backup\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: LOG_BACKUP","datePublished":"2014-01-15T16:00:22+00:00","dateModified":"2014-11-13T18:45:54+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/"},"wordCount":783,"commentCount":0,"keywords":["backup","log backup","SQL Server","transaction log","transaction log reuse","Transactions"],"articleSection":["Backup","General","Log Reuse Wait","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/","url":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/","name":"Log Reuse Waits Explained: LOG_BACKUP - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-15T16:00:22+00:00","dateModified":"2014-11-13T18:45:54+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting LOG_BACKUP as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1830\/log-reuse-waits-explained-log_backup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: LOG_BACKUP"}]},{"@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-tw","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1830","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=1830"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1830\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1830"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1830"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1830"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}