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

To set the expiration period for a subscription to a merge 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

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