how to prevent replication to stop if there is no activity for some time?

Posted on

Question :

On one of my transaction replication publications, when there is no any activity, after some time, the replication stops.

Where can I see these replication settings?

what to put in place to prevent this to happen?

enter image description here

enter image description here

Answer :

BOL has it documented :

Subscriptions can be deactivated or can expire if they are not synchronized within a specified retention period.

If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.

If a subscription is not synchronized within the publication retention period (default of 336 hours), the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher. The subscription must be recreated and synchronized.

Where can I see these replication settings?

You can see them at distribution database by running sp_helpdistributiondb

what to put in place to prevent this to happen?

Do it programatically as I have described in my answer here.

or

You can adjust the max_distretention value for sp_adddistributiondb which is 72hrs default to a large value depending on your organization’s need.

Leave a Reply

Your email address will not be published. Required fields are marked *