Log Reuse Waits Explained: REPLICATION

Introduction

There 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

Replication

Transactional 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.

Under 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.

The 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.

There 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.

Waiting for the Log Reader Agent

The 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 value of REPLICATION .

To resolve the REPLICATION 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.

Change Data Capture

Change 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.

This 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 value of REPLICATION 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.

Summary

Transactional 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 value of REPLICATION. Problems with change data capture can also lead to this wait type as CDC is using the replication technology stack.

Log Reuse Wait Series

This post is part of a short series about circumstances that prevent transaction log truncation.
Below is a list of links to the posts that are already available.