In my current environment, I have a push transactional replication and I wanted to find a way to help minimize performance. So came about the -maxDeliveredTransactions. I read the technet article which states:
-MaxDeliveredTransactions number_of_transactions
Is the maximum number of push or pull transactions applied to Subscribers in one synchronization. A value of 0 indicates that the maximum is an infinite number of transactions. Other values can be used by Subscribers to shorten the duration of a synchronization being pulled from a Publisher.
In the apress pro sql server 2005 replication book, it states:
Distrib [-MaxDeliveredTransactions]: This sets the maximum number of transactions that are sent to the subscribing server during one synchronization. A value of 0 means an infinite number of transactions can be sent. We should consider resetting this to an optimum value, such as 1000, to shorten the period after which transactions are sent, and
hence improve performance.
Basically a rip off from BOL but a bit more information about helping with performance. Reading this and other resources, I figured this would be a great thing to have in my environment. Then I noticed the following:
- if I set the -maxDeliveredTransactions to 5000, the push replication would replicate up to 5000 commands and then gracefully stopped. Then I had to restart the subscription and again replication gracefully stops at 5000. Now I’m confused because I have a continous push replication setup. As long as it is continous, it should still be considered ‘one synchronization.’ MS internal documentation state otherwise. It wasn’t useful in my case and I set it back to 0. This setting is useful if you aren’t running a continous replication.