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

Transaction Log<\/h3>\n

\nSQL Server uses the transaction log for two purposes. First the transaction log is used for SQL Server to be able to guaranty the durability requirement of the ACID<\/a> properties. For that it makes sure that at least the portion of the log containing the active transaction has been written to disk successfully before that transaction can be committed.\n<\/p>\n

\nThe second use of the transaction log affects rollbacks. During a transaction that changes a particular page, another transaction might change and commit another change on the same page. Because of that, SQL Server cannot just declare pages that were changed by a transaction as unusable and reload them from disk the next time they are needed. Instead all the changes applied by a transaction need to be undone step by step in reverse order when the transaction is rolled back.\n<\/p>\n

Waiting for a Transaction<\/h3>\n

\nFor this rollback process SQL Server uses the information captured in the transaction log. Therefore it cannot reuse a virtual log file that contains transaction log records of a transaction that is still active. SQL Server will return a log_reuse_wait_desc<\/span> value of ACTIVE_TRANSACTION <\/span> if it runs out of virtual log because of that.\n<\/p>\n

\nTo resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions are usually short lived, but there are many reasons that can turn a normal transaction into a log running one. If you cannot afford to wait for an extra-long running transaction to finish, you might have to kill its session. However, that will cause that transaction to be rolled back. Keep this in mind when designing your application and try to keep all transactions as short as possible.\n<\/p>\n

\nOne common design mistake that can lead to very long running transactions is to require user interaction while the transaction is open. If the person that started the transaction went to lunch while the system is waiting for a response, this transaction can turn into a very-long-running transaction. During this time other transactions, if they are not blocked by this one, will eventually fill up the log and cause the log file to grow.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server will return a log_reuse_wait_desc<\/span> value of ACTIVE_ TRANSACTION<\/span> if it runs out of virtual log files because of an open transaction. Open transactions prevent virtual log file reuse, because the information in the log records for that transaction might be required to execute a rollback operation.\n<\/p>\n

\nTo prevent this log reuse wait type, make sure you design you transactions to be as short lived as possible and never require end user interaction while a transaction is open.\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_TRANSACTION 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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[5,61,27,62],"tags":[15,63,64,17],"class_list":["post-1839","post","type-post","status-publish","format-standard","hentry","category-general","category-log-reuse-wait","category-series","category-transaction-log","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse","tag-transactions"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: ACTIVE_TRANSACTION - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION 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\/1839\/log-reuse-waits-explained-active_transaction\/\" \/>\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_TRANSACTION - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION 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\/1839\/log-reuse-waits-explained-active_transaction\/\" \/>\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-17T16:00:42+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:45:41+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: ACTIVE_TRANSACTION\",\"datePublished\":\"2014-01-17T16:00:42+00:00\",\"dateModified\":\"2014-11-13T18:45:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/\"},\"wordCount\":592,\"commentCount\":0,\"keywords\":[\"SQL Server\",\"transaction log\",\"transaction log reuse\",\"Transactions\"],\"articleSection\":[\"General\",\"Log Reuse Wait\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/\",\"name\":\"Log Reuse Waits Explained: ACTIVE_TRANSACTION - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-17T16:00:42+00:00\",\"dateModified\":\"2014-11-13T18:45:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1839\\\/log-reuse-waits-explained-active_transaction\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: ACTIVE_TRANSACTION\"}]},{\"@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_TRANSACTION - sqlity.net","description":"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION 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\/1839\/log-reuse-waits-explained-active_transaction\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: ACTIVE_TRANSACTION - sqlity.net","og_description":"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-17T16:00:42+00:00","article_modified_time":"2014-11-13T18:45:41+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":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: ACTIVE_TRANSACTION","datePublished":"2014-01-17T16:00:42+00:00","dateModified":"2014-11-13T18:45:41+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/"},"wordCount":592,"commentCount":0,"keywords":["SQL Server","transaction log","transaction log reuse","Transactions"],"articleSection":["General","Log Reuse Wait","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/","url":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/","name":"Log Reuse Waits Explained: ACTIVE_TRANSACTION - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-17T16:00:42+00:00","dateModified":"2014-11-13T18:45:41+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting ACTIVE_TRANSACTION as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1839\/log-reuse-waits-explained-active_transaction\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: ACTIVE_TRANSACTION"}]},{"@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-tF","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1839","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=1839"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1839\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}