Replication Cleanup

When a replication is set up, a replication cleanup task is automatically created on the distribution server for each publisher/subscriber pairing. For example, if three publishers use one remote distributor, and if one subscriber subscribes to various publications from all three publishers, then there are three cleanup tasks on the distributor (one to support each publisher/subscriber pairing).

Replication cleanup is an important task that helps replication to effectively function in a long-running environment. The replication cleanup task:

• Retains transactions for each subscribed database for a defined retention period after all subscription servers have received them.
• Deletes associated .TMP files for initial sync events when this transaction is removed.

There is a parameter associated with the cleanup task¾the retention period¾that determines the period of time that transactions will be guaranteed to be retained within the distribution database after they have been distributed to a destination database. The setting of the retention period, in conjunction with scheduling implemented for backup of a subscription server, can be used to ensure that the information required to automatically recover a destination database is always available within the distribution database.

For example, if the subscription server POODLE has scheduled a transaction log dump of a destination database for once every 24 hours, you could set the retention period for POODLE's cleanup task to 48 hours. Then, even if POODLE experienced a catastrophic failure immediately before a scheduled backup, after restoring the server from the last available transaction log dump, all transactions necessary to automatically restore the replicated tables would still be available to the distribution process of the distribution server.

Setting the Retention Period

The first subscription established between a publisher/subscriber pair causes a replication cleanup task to be created on the distributor. This task is created using the existing setting for the retention period. Because of this, it is best to set the retention period before establishing any subscriptions for that publisher/subscriber pair.

    To set the retention period for a subscription server before any subscriptions are established

1. In the Server Manager window select the publication server, from the Server menu choose Replication Configuration, and from the drop-down menu that appears choose Publishing.
2. In the Replication Publishing dialog box, select the subscription server, then select the Distribution Options button.

   The Distribution Options dialog box appears.

   

3. Enter a number (representing hours) in the Retention Period After Distribution box.
4. Choose OK.

If the replication cleanup task already exists, you can (without dropping subscriptions) change the retention period by editing the cleanup task on the distributor.

    To change the retention period for an existing cleanup task

1. In the Server Manager window select the distribution server, and then from the Tools menu choose Task Scheduling.

   The Task Scheduling dialog box appears.

   

2. Select the cleanup task for the appropriate publisher/subscriber pair.

   For example AIREDALE_TERRIER_Cleanup.

3. Select the Edit Task button.

   The Task Editing dialog box appears.

   

4. In the command line, change the retention period setting.

   There are four parameters in the command line for the cleanup task:

   • The cleanup stored procedure, sp_replcleanup.
   • The publication server name (for example, TERRIER).
   • The subscription server name (for example, AIREDALE).
   • The retention period setting. The default is 0, which removes all distributed transactions whenever the cleanup task is run.

   Enter a new retention period value. The number you enter represents hours. For example, type 48 to indicate a retention period of 48 hours.

5. Choose the Modify button.