More on merge replication with subscription expiring after a period of time

We have a merge replication topology with pull subscriptions. We have a setting to expire the subscriptions that haven’t synchronized in the past X days. This setting was mainly due to optimizations, when you don’t expire your subscriptions the amount of metadata to be used grows and grows and your merge process suffers.

The drawback on this setting is that it also makes the snapshot obsolete after X days for any new or reinitialized subscription.

When you add a new subscription to your publication and the snapshot was generated X-1 days ago you will have the following error:

The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.

At first we wondered why we got this error when the rest of the subscriptions were working just fine and also wondered what impact would have on the existing subscriptions to regenerate the snapshot.

The answer is: no impact.

We got the explanation from one of Hilary Cotter‘s replies:

The snapshot contains all the data, schema and metadata required to create
all the objects on the subscriber. After one day in your case the metadata
on the publisher is purged and what is in the snapshot will not be
sufficient to sync with the publisher, hence you need a new snapshot.

You want to set a small number so that not a lot of data goes across the
wire, but a big enough number so that the majority of your subscribers will
sync within the expiration time. If you set it to an infinite amount – never
expires, a lot of data will have to go to the subscriber to get it back in

And another reply with further clarifications:

The answer lies in the MSmerge_contents and MSmerge_genhistory tables.
These two tables hold the list of data changes that happened for the
past x days, x being the subscription expiration days. After x days the
record of the data change expire from the MSmerge_contents table. The
implication of that is that existing subscriptions that have not
synchronised for the past x days will then not be able to merge that
change anymore. The same holds true for creating new subscriptions with
an old snapshot – remember the snapshot also contains data. If the
snapshot was created x-2 days ago you will missing two days of data
changes that has already expired from the MSmerge_contents table.


a thread on MSDN:

2 thoughts on “More on merge replication with subscription expiring after a period of time”

  1. Oops, I forgot, a good maintenance task would be running the snapshot agent every X days to avoid problems when adding new subscriptions to the existing publication.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.