Set the Expiration Period for Subscriptions
This topic describes how to set the expiration period for subscriptions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. The expiration period for subscriptions determines the period of time before a subscription expires and is removed. For more information, see Subscription Expiration and Deactivation.
In This Topic
The subscription expiration period is also referred to as the publication retention period. Cleanup of merge replication metadata is dependent on this setting:
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.
The retention period for merge publications has a 24-hour grace period to accommodate Subscribers in different time zones. If, for example, you set a retention period of one day, the actual retention period is 48 hours.
It is possible to specify that subscriptions never expire, but it is strongly recommended that you do not use this value, because metadata cannot be cleaned up.
Set the expiration period for subscriptions on the General page of the Publication Properties - <Publication> dialog box. For more information about accessing this dialog box, see View and Modify Publication Properties.
To set the expiration period for subscriptions
In the Subscription expiration section on the General page of the Publication Properties - <Publication> dialog box, specify whether subscriptions should expire.
If they should expire, specify an expiration time period.
You can use replication stored procedures to either set this value when a publication is created or modify this value at a later time.
To set the expiration period for a subscription to a snapshot or transactional publication
To set the expiration period for a subscription to a merge publication
At the Publisher, execute sp_addmergepublication. Specify the desired value for the subscription expiration period for @retention. Specify the units in which the expiration period is expressed for @retention_period_unit, which can be one of the following:
1 = week
2 = month
3 = year
The default expiration period is 14 days. For more information, see Create a Publication.
To change the expiration period for a subscription to a snapshot or transactional publication
At the Publisher, execute sp_changepublication. Specify retention for @property and the new subscription expiration period, in hours, for @value.
To change the expiration period for a subscription to a merge publication
At the Publisher, execute sp_helpmergepublication, specifying @publication and @publisher. Note the value of retention_period_unit in the result set, which can be one of the following:
0 = day
1 = week
2 = month
3 = year
At the Publisher, execute sp_changemergepublication. Specify retention for @property and the new subscription expiration period, as text based on the retention period unit from step 1, for @value.
(Optional) At the Publisher, execute sp_changemergepublication. Specify retention_period_unit for @property and a new unit for the subscription expiration period for @value.