How Merge Replication Manages Subscription Expiration and Metadata Cleanup

  • Article

A subscription to a merge publication expires if it has not synchronized with the Publisher within the publication retention period. The default retention period is 14 days; it is set using the @retention and @retention_period_unit parameters of sp_addmergepublication (Transact-SQL). The @retention_period_unit requires the publication compatibility level to be 90RTM or higher. For Subscribers running previous versions of Microsoft SQL Server, @retention_period_unit is always set to 'day'. For more information about compatibility level, see the "Compatibility Level for Merge Publications" section in the topic Using Multiple Versions of SQL Server in a Replication Topology.

When a subscription expires, it must be reinitialized, because metadata for the subscription is removed (see the section "Metadata Cleanup" in this topic for more information). Subscriptions that are not reinitialized are dropped by the Expired Subscription Cleanup job that runs on the Publisher. By default, this job runs daily; it removes all push subscriptions that have not synchronized for double the length of the publication retention period. For example:

  • If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days.
    If the Publisher is running SQL Server 2005 and the agent for the subscription is from SQL Server 2005, a subscription only expires if there have been changes to the data in that subscription's partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only if there have been changes to the German customer data in the last 14 days.
  • From 14 days to 27 days after the last synchronization, the subscription can be reinitialized.
  • At 28 days after the last synchronization, the subscription is dropped by the Expired Subscription Cleanup job. If a push subscription expires, it is completely removed, but pull subscriptions are not. You must clean up pull subscriptions at the Subscriber. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming).

Considerations for Setting the Publication Retention Period

Keep the following in mind when setting the retention period for merge publications:

  • Cleanup of merge replication metadata is dependent on the publication retention period:
    • Replication cannot clean up metadata in the publication and subscription databases until the retention period is reached. Use caution in specifying a high value for the retention period, because it can negatively impact replication performance. It is recommended that you use a lower setting if you can reliably predict that all Subscribers will synchronize regularly within that time period.
    • It is possible to specify that subscriptions never expire (a value of 0 for @retention), but it is strongly recommended that you do not use this value, because metadata cannot be cleaned up.
  • The retention period for any republisher must be set to a value equal to or less than the retention period set at the original Publisher. If you use alternate synchronization partners, you should use the same publication retention values for the Publishers and all alternate synchronization partners. Using different values might lead to non-convergence. If you need to change the publication retention value, reinitialize the Subscriber to avoid the non-convergence of data.
  • If, after a clean up, the publication retention period is increased and a subscription tries to merge with the Publisher (which has already deleted the metadata), the subscription will not expire because of the increased retention value. However, the Publisher does not have enough metadata to download changes to the Subscriber, which leads to non-convergence.

Metadata Cleanup

Metadata cleanup in merge replication is performed by the stored procedure sp_mergemetadataretentioncleanup (Transact-SQL); the timing of cleanup is based on the publication retention period. Every time the Merge Agent runs for a subscription, it calls the cleanup procedure. The procedure removes metadata older than the publication retention period from the following system tables:

These tables are used by all publications in a publication database: if there is more than one publication, the longest retention period is always used to determine when metadata can be removed.

See Also

Concepts

How Merge Replication Works
Reinitializing a Subscription

Help and Information

Getting SQL Server 2005 Assistance