Holger Linke Microsoft SQL Server Escalation Services
This article describes an issue with SQL Server Transactional Replication that was detected recently. If you have configured the Log Reader agent to use the MaxCmdsInTran parameter, and also have configured the Distribution database to use the “sync with backup” option, you may experience a failure of the Log Reader Agent, or find that not all changes have been replicated to your subscribers.
Consider a scenario where you encounter all of the following conditions:
The issue might then occur after the next start of the Log Reader Agent, when the agent will process the pending transactions, and will attempt to split the large transaction according to the MaxCmdsInTran value.
If you are affected by this issue, you will see one or more of the following symptoms:
Typically, from the large transaction, only the first MaxCmdsInTran sub-transaction would be picked up by the Log Reader; all other commands from the large transaction would be skipped. The smaller transactions will always remain unaffected, and all their data will be replicated.
Once you are affected by this issue, the Publisher and its Subscribers will remain out of sync. This usually requires a reinitialization of your topology.
Associated Error Messages:
Log Reader Agent errors:
Distribution Agent error:
These errors might occur much later if any of the skipped rows from the large transaction is updated or deleted; or re-inserted if the skipped command was a Delete.
Possible solutions for this issue are investigated, but it is still open if and when a solution will become available.
This Log Reader parameter specifies the maximum number of statements grouped into a transaction, as the Log Reader writes commands to the distribution database. By default, this parameter is not set so that thetransaction boundaries of the Publisher are preserved. Using this parameter allows the Log Reader Agent to divide large transactions at the Publisher into several smaller transactions. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity.
Originally, MaxCmdsInTran was not designed to be always turned on. It exists in the product to work around cases where a large number of DML operations were performed in a single transaction by accident. If you are routinely falling into this situation, you should seriously look into ways to reduce the size of your transactions.
Setting this option on the distribution database ensures that transactions in the log of the publication database will not be truncated until they have been backed up at the distribution database. The distribution database can be restored to the last backup, and any missing transactions are delivered from the publication database to the distribution database. Replication continues unaffected. Setting this option on the distribution database does not affect replication latency. However, the option will delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up. (This can create a larger transaction log in the publication database.).
We recommend that you always set this option on the distribution database.
The previous documentation specifically advertised MaxCmdsInTran to enhance replication performance, for example in the following paragraph:
Enhance Transactional Replication Performancehttp://technet.microsoft.com/en-us/library/ms151762.aspxDistribution Agent and Log Reader Agent ParametersUse the MaxCmdsInTran parameter for the Log Reader Agent.
This recommendation is no longer correct and has been updated in the documentation now. You should not use the MaxCmdsInTran parameter as a measure to improve the overall Log Reader performance. It should not remain activated permanently, e.g. in order to resolve ongoing, day-to-day performance bottlenecks of the Log Reader.