Reducing the size of the distribution database & improving transactional replication performance: Part 2

The impact of having data sitting in the distribution database that doesn’t need to be there can be significant.  The aim of this series of articles is show you how to keep the distribution database as small (and efficient) as possible, and to keep the impact of the clean-up job to a minimum.  Each of the steps outlined will help reduce the data that is unnecessarily held in the distribution database, and free up resources for other things.

Part 1 of this article focussed on the intricacies of SQL Server transactional replication, and made the observation that the clean-up mechanism treats publications as a collective rather than individuals.  Part 1 looked into publication settings, and part 2 looks at how distribution job schedules can cause unnecessary bloating in the distribution database.

When the distribution clean-up job runs it executes the sp_MSdistribution_cleanup procedure.  This procedure then executes a number of other procedures as part of the clean-up process.  The most important procedure in terms of what will actually be removed is sp_MSmaximum_cleanup_seqno.  This procedure finds the maximum transaction sequence number to be removed from the distribution DB, and uses the following process to find this:

  1. Find the most recent synchronisation for each publication in the MSdistribution_history table.*
  2. Find the oldest of all of the synchronisations from step 1.
  3. Find the most recent transaction from MSrepl_transactions that is older than the synchronisation from step 2.  This is the latest transaction to remove from the distribution DB.

* The MSdistribution_history table holds details of all synchronisations within the transaction retention period (default of 72 hours).  If any distribution agent has not synchronised during this time then the oldest value in MSsubscriptions is used for that distribution agent.

Differing distribution schedules

If, for example, you have two distributions, one with a schedule of once a minute, and one with a schedule of once a day you will see something similar to the following:

  1. Find the most recent synchronisation for each publicationin the MSdistribution_history table.
    • You will have one synchronisation up to one minute old, and one synchronisation up to one day old.
  2. Find the oldest of the synchronisations from step 1.
    • This will most likely be the synchronisation up to one day old.
  3. Find the most recent transaction that is older than the synchronisation from step 2.  This is the latest transaction to remove from the distribution DB.

In this specific example the MSrepl_transactions and MSrepl_commands tables will only be cleaned up once per day.  For the rest of the day these tables will be filling up and the clean-up job will be running, reading more data and taking more resources each time it runs, but deleting nothing.

The resolution to this is simple; just set all of the distribution job schedules to be the same or similar.

Part 3 of the series focuses on replicating intensive stored procedure executions rather than the outcome of the execution.

4 Responses
  1. shwetha

    The distribution database is growing and the clean up job is running for long hours.
    changed the subscription never expire and set the retention period to 96 hrs and ran the cleanup job , still no luck.
    immidiate sync and allow anonymous is set to false.still no luck.
    is there any other way to find the data is replicating very slow from distributor to subscriber.
    appreciate your help on this.

    Thank you

    1. Hi Shwetha. How many publications do you have? You need to apply these changes to all publications and distributions within your replication environment otherwise the problem will remain…

        1. You’ll need to apply all of the changes to all publications even if it seems there is only a problem with one publication. This is because the cleanup mechanism looks at the publications as a group. You should put all of them on a similar distribution schedule (one as short as possible) to avoid holding too many transactions in the distribution database. That is the key to resolving your performance issues. Once you’ve reduced the size of the database your replication performance should improve significantly.

Leave a Reply