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

Replication<\/h3>\n

\nTransactional replication works similar to mirroring in high-performance mode. Changes applied and committed in one database can be replicated (hence the name) in another database. The main difference to mirroring is that transactional replication is a lot more flexible. Mirroring always copies the entire database and allows only for one secondary. In transactional replication on the other hand, you can have many subscribers (receivers of changes) and you do not have to send the entire database. You can for example send only a subset of the tables or even a subset of the columns of one table.\n<\/p>\n

\nUnder the covers transactional replication uses the same base technology as mirroring. The Log Reader Agent scans the transaction log of the publication database (the source database) and sends all committed changes that affected published objects over to the subscribers.\n<\/p>\n

\nThe data is actually not directly sent to the subscribers but instead to a distributor. The distributor then distributes the changes to the subscribers. Under-sizing the distributor often is a cause for bottlenecks.\n<\/p>\n

\nThere are two other forms of replication: Snapshot replication and merge replication. Both do not use the transaction log, so they are not relevant for this discussion. For more details on the inner workings of replication and the different replication types see my book Fundamentals of SQL Server 2012 Replication<\/a>.\n<\/p>\n

Waiting for the Log Reader Agent<\/h3>\n

\nThe Log Reader Agent is responsible for scanning the transaction log and sending the data on. If that agent cannot keep up with the load, log truncation cannot occur. In this case SQL Server will return a log_reuse_wait_desc<\/span> value of REPLICATION <\/span>.\n<\/p>\n

\nTo resolve the REPLICATION<\/span> wait type, make sure the network connection to the distributor is working and the distributor can handle the current work load. If the publisher and the distributor are on the same SQL Server instance or on the same physical machine, consider moving the distributor to dedicated hardware.\n<\/p>\n

Change Data Capture<\/h3>\n

\nChange Data Capture or CDC is a technology designed to record all changes that were applied to a monitored table or set of tables. This information can for example be used in an audit report providing the before and after values for every change as well as who executed the change and when it was applied.\n<\/p>\n

\nThis very powerful technology makes use of the replication technology. That means if CDC gets behind for some reason, e.g. because of a degrading raid array causing slow performance of the drive it is writing to, and if this is preventing log truncation a log_reuse_wait_desc<\/span> value of REPLICATION<\/span> is reported. So if you see this value and do not use replication or can't find anything wrong with it, check your CDC setup.\n<\/p>\n

Summary<\/h3>\n

\nTransactional replication uses a log reader agent to read and process all committed transactions in the publication database. Virtual log files containing log records that have not been processed yet cannot be reused. If the log reader agent falls behind, SQL Server will eventually run out of virtual log files. In this case it will return a log_reuse_wait_desc<\/span> value of REPLICATION<\/span>. Problems with change data capture can also lead to this wait type as CDC is using the replication technology stack.\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 REPLICATION 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,77,27,62],"tags":[79,78,15,63,64,17],"class_list":["post-1846","post","type-post","status-publish","format-standard","hentry","category-general","category-log-reuse-wait","category-replication","category-series","category-transaction-log","tag-change-data-capture","tag-replication-2","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse","tag-transactions"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: REPLICATION - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting REPLICATION 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\/1846\/log-reuse-waits-explained-replication\/\" \/>\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: REPLICATION - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting REPLICATION 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\/1846\/log-reuse-waits-explained-replication\/\" \/>\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-19T16:00:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:43: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\\\/1846\\\/log-reuse-waits-explained-replication\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: REPLICATION\",\"datePublished\":\"2014-01-19T16:00:24+00:00\",\"dateModified\":\"2014-11-13T18:43:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/\"},\"wordCount\":683,\"commentCount\":5,\"keywords\":[\"Change Data Capture\",\"replication\",\"SQL Server\",\"transaction log\",\"transaction log reuse\",\"Transactions\"],\"articleSection\":[\"General\",\"Log Reuse Wait\",\"Replication\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/\",\"name\":\"Log Reuse Waits Explained: REPLICATION - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-19T16:00:24+00:00\",\"dateModified\":\"2014-11-13T18:43:41+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting REPLICATION as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1846\\\/log-reuse-waits-explained-replication\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: REPLICATION\"}]},{\"@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: REPLICATION - sqlity.net","description":"Is your database log file growing out of bound and reporting REPLICATION 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\/1846\/log-reuse-waits-explained-replication\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: REPLICATION - sqlity.net","og_description":"Is your database log file growing out of bound and reporting REPLICATION as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-19T16:00:24+00:00","article_modified_time":"2014-11-13T18:43: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\/1846\/log-reuse-waits-explained-replication\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: REPLICATION","datePublished":"2014-01-19T16:00:24+00:00","dateModified":"2014-11-13T18:43:41+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/"},"wordCount":683,"commentCount":5,"keywords":["Change Data Capture","replication","SQL Server","transaction log","transaction log reuse","Transactions"],"articleSection":["General","Log Reuse Wait","Replication","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/","url":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/","name":"Log Reuse Waits Explained: REPLICATION - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-19T16:00:24+00:00","dateModified":"2014-11-13T18:43:41+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting REPLICATION as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1846\/log-reuse-waits-explained-replication\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: REPLICATION"}]},{"@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-tM","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1846","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=1846"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1846\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1846"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1846"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1846"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}