How to: Set the Expiration Period for Subscriptions (Replication Transact-SQL Programming)

The expiration period for subscriptions, also known as the retention period, determines the period of time before a subscription expires and is removed. For more information, see Subscription Expiration and Deactivation. 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

1. 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 How to: Create a Publication (Replication Transact-SQL Programming).

To set the expiration period for a subscription to a merge publication

1. 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 How to: Create a Publication (Replication Transact-SQL Programming).

To change the expiration period for a subscription to a snapshot or transactional publication

1. 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

1. 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

2. 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.

3. (Optional) At the Publisher, execute sp_changemergepublication. Specify retention_period_unit for @property and a new unit for the subscription expiration period for @value.

See Also

Tasks

How to: Create a Publication (Replication Transact-SQL Programming)

Concepts

Programming Replication Using System Stored Procedures

Other Resources

Subscription Expiration and Deactivation

Help and Information

Getting SQL Server 2005 Assistance