Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Subscription Deactivation and Expiration

Subscription expiration and deactivation are the other concepts you must be familiar with to maintain replication running smoothly. Replicated transactions reside in the distribution database until they're delivered to the subscribers. The distribution cleanup agent runs every 10 minutes (by default) to see which transactions have already been delivered—such transactions are discarded. If the distribution agent has trouble connecting to subscriber(s) or if it is not run for a long time, the cleanup agent can't remove any transactions from the distribution database. Therefore, the distribution database will keep growing. If the default threshold of delivering transactions within 72 hours is exceeded, the subscription will be marked as deactivated. If this happens you must do the follwong:

  1. Figure out what is going on with the distribution agent. Perhaps it doesn't have appropriate permissions to connect to the subscriber(s)?

  2. Reinitialize the deactivated subscriptions and run the snapshot agent for each of them. After you get the distribution agent running again, the new snapshot will be applied to each subscriber.

Notice that if there is no transactional activity on the publisher, there are no transactions to deliver to subscribers. Therefore, the subscriptions won't be marked as deactivated even if the distribution agent does not run for 72 hours.

Subscription deactivation might seem like a harsh penalty for not being able to run the distribution agent, but if you can't synchronize transactions in 72 hours, you are probably better off by getting a new snapshot to your subscribers, anyway.

If you keep the deactivated subscriptions without paying attention, they will be marked as expired. The default number of hours for marking a subscription as expired is 336. Such publications will be dropped automatically by the Expired Subscriptions Cleanup Agent. By default, such an agent runs once a day. Once the subscription is dropped, you must re-create it before transactions can get delivered to the subscribers again.

You can change the default number of hours before the subscription is marked as expired from the General tab of the Publication Properties screen, as shown in the following screen.

Figure 8Figure 8

Notice that you can mark subscription so that it will never expire. However, the Distribution Cleanup Agent can still mark them as deactivated. If you prevent the subscription from expiration, the distribution database can grow very large. Generally, you can achieve better performance if you don't leave transactions in the distribution database for longer than a few hours.

So the point of subscription expiration is to maintain a reasonable size of the distribution database and reasonable latency in delivering transactions to subscribing servers. Be sure to configure the distribution agent to notify you through e-mail or pager each time it fails, so that you can figure out what's wrong with it and fix it as soon as possible.

  • + Share This
  • 🔖 Save To Your Account