Set the Expiration Period for Subscriptions
This topic describes how to set the expiration period for subscriptions in SQL Server 2012 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
Before you begin:
Recommendations
To set the expiration period for subscriptions, using:
SQL Server Management Studio
Transact-SQL
Before You Begin
Recommendations
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.
[Top]
Using SQL Server Management Studio
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.
[Top]
Using Transact-SQL
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
- At the Publisher, execute sp_addpublication. Specify the desired subscription expiration period, in hours, for @retention. The default expiration period is 336 hours. For more information, see Create a 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.
[Top]
Zobacz także
Koncepcje
Programming Replication Using System Stored Procedures