0

We are running push transactional replication in our production environment ( SQL server 2014 (x64) Default configuration). It hangs when thousands of live transactions flows in and this continues for hours and eventually days. So as to come out we generally stop the replication. I am not sure what causes it and also don't know how to troubleshoot this issue. Since I am new to replication, can anybody help me out with the steps to troubleshoot the issue?

1
  • Are the dbs part of replication ?
    – Kin Shah
    Jun 1 '18 at 12:28
1

I've experienced a similar problem. It turned out to be an issue with when the "Agent history clean up: distribution" job kicks-off on the distributor. This job would hang for a long time and bring replication to a stand-still when there were a lot of transactions in the queue. Check the History for the Job to see if it's stalling at the same time you have your problems.

The solution we implemented was to push through transactions in Batches with a WAIT between batches. we had to experiment with the batch size and Wait time to get the sweet-spot that didn't cripple the distributor.

1
  • I've found that there are some commands which take more than 4 minutes to apply on the subscriber. The issue is mainly applying the commands from distributor-subscriber. Also, there is another replication is running using the same source db. The publisher and distributor is on the same serverand the subscriber is on another server. ie. both the replication is using the same disdtributor database. Any suggestions please.
    – Anitha
    Jun 7 '18 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.