none
sp_MSdistribution_cleanup parameters

    Question

  • Hi,
    What does the min_distretention parameter actually mean when calling the sproc sp_MSdistribution_cleanup?
    I understand that the max_distretention parameter means the number of hours after which pending replication commands will be deleted (causing the subscriber to require reinitialisation) - but what does the min_ parameter indicate?

    TIA,

    Moff.
    Friday, October 19, 2007 4:48 PM

Answers

  • Max is the maxiumn amount of time commands will be stored in the distribution database IF subscribers have not picked them up, or if you are using anonymous subscribers.

    Min, is the minimum amount of time commands will be stored in the distribution after they have been replicated to named subscribers.
    Friday, October 19, 2007 5:19 PM
    Moderator
  • No, min means that 0 seconds after the commands are replicated to all names subcribers the commands can be replicated by the distribution clean up task - which runs every 10 minutes. So it means that if it is 0 it could be deleted anywhere from 0 seconds to 10 minutes.

    I would set it to 0 and set my max to 72 hours. This way if you go away on a three day weekend and your replication agents fail at Friday at 5:00 pm when you come back on Monday at 9:00 there still is a chance that you can get everything going again and send all the commands down to the subscriber. If the subscriber is offline for more than the lesser of the publication retention period or the history retention period  your subscription will be expired.

    HTH
    Friday, October 19, 2007 8:22 PM
    Moderator
  • The unit is hours. 0 hours is 0 seconds. So in theory if the distribution clean up agent runs just as the distribution agent completes, these commands will remain in the distribution database 0 seconds after they have been replicated.

    You are correct that I did mean deleted where I wrote replicated.

    If you are using anonymous subscriptions this might explain why you are seeing them hanging around for 72 hours.


    Monday, October 22, 2007 3:39 PM
    Moderator

All replies

  • Max is the maxiumn amount of time commands will be stored in the distribution database IF subscribers have not picked them up, or if you are using anonymous subscribers.

    Min, is the minimum amount of time commands will be stored in the distribution after they have been replicated to named subscribers.
    Friday, October 19, 2007 5:19 PM
    Moderator
  • Hi Hilary,
    Thanks for the swift reply.

    If min_distretention is zero, does that mean commands (whether replicated or not) are retained until max_distretention is reached?
    We've got push subscriptions only, so could we reduce the size of the msrepl_commands table by setting min_distretention to a positive value? eg. if we set it to 1 and max_distretention to 24 would that mean when the cleanup job ran it would remove all commands that had been replicated over an hour ago, and all commands that are older than 24 hours regardless of whether they've been replicated or not?

    Thanks, Robin.

    Friday, October 19, 2007 7:32 PM
  • No, min means that 0 seconds after the commands are replicated to all names subcribers the commands can be replicated by the distribution clean up task - which runs every 10 minutes. So it means that if it is 0 it could be deleted anywhere from 0 seconds to 10 minutes.

    I would set it to 0 and set my max to 72 hours. This way if you go away on a three day weekend and your replication agents fail at Friday at 5:00 pm when you come back on Monday at 9:00 there still is a chance that you can get everything going again and send all the commands down to the subscriber. If the subscriber is offline for more than the lesser of the publication retention period or the history retention period  your subscription will be expired.

    HTH
    Friday, October 19, 2007 8:22 PM
    Moderator
  • Hi Hilary,
    Could you review your first paragraph please - I think 'seconds' and 'replicated' are typos for hours and deleted respectively? (although zero seconds is the same as zero hours)

     Hilary Cotter wrote:
    No, min means that 0 seconds after the commands are replicated to all names subcribers the commands can be replicated by the distribution clean up task - which runs every 10 minutes. So it means that if it is 0 it could be deleted anywhere from 0 seconds to 10 minutes.


    Are you certain that a min_distretention of zero does mean that the cleanup job will delete the commands once they've been replicated and the cleanup job runs?
    The reason that I ask is that the earliest value of entry_date on our MSrepl_transactions is precisely 72 hours prior to when the cleanup job ran. Our min_distretention is set to 0 and max_distretention to 72. All the distribution agents are up to date, so I would have expected the earliest value of entry_date to be within a few minutes or hours at most of when the cleanup job ran, do you agree?

    Many thanks,

    Robin.
    Monday, October 22, 2007 8:22 AM
  • The unit is hours. 0 hours is 0 seconds. So in theory if the distribution clean up agent runs just as the distribution agent completes, these commands will remain in the distribution database 0 seconds after they have been replicated.

    You are correct that I did mean deleted where I wrote replicated.

    If you are using anonymous subscriptions this might explain why you are seeing them hanging around for 72 hours.


    Monday, October 22, 2007 3:39 PM
    Moderator
  • Hi Hilary,
    Thanks for the reply.
    You are right, of course - it turns out we do have some publications set up as anonymous. Because we don't use them as such I'd assumed that they wouldn't be set up as anonymous.

    Cheers, Robin.
    Tuesday, October 23, 2007 10:38 AM