Post reply

Distribution Clean Up Agent Problem

  • November 5, 2004 at 10:20 am

    #87021

    Hi,

     I have searched far and wide and found nothing on this. 

    I have  a distribution clean up agent that is taking between 1 hour and 3 hours to run (is this normal? It's scheduled to run every 10 mins!).  This causes blocking on some other replication agents which cause retries and on some infrequent but annoying occasions complete timeouts (those nasty red crosses!) and the jobs need to be restarted, which is no problem, but i am rarely about at 3am to do this (i have a life away from SQL thank god!). 

    I have thought of creating a job that would check for failures on these agents and restart them, but my system table knowledge is not good enough to be able to do this (have looked at sysjobs and sysjobhistory, but how do i restart?)

    Is there a replication setting I can change, is my distribution clean up agent behaving normally?  It is set to min 0, max 120 hours transaction retention and is deleting >150,000 records at a time

    Thanks in advance

     

  • November 8, 2004 at 6:11 am

    #529248

    What is the retention time in the distribution database?

    To know, right click over replication in EM, Configure Publishing, ..., and then Properties of the distribution database.

    Where the values over there changed recently?, Maybe you where saving many days, and if you changed to a few days, then the agents, are deleting history old transactions.

     

    Also check the size of the distribution DB.

  • November 8, 2004 at 6:45 am

    #529254

    Distribution Database Properties Values:

     

    Store the transactions:

    At least: 0 hours

    But not more than : 120 hours

    History retention

    Store replication performance history at least: 24 hours

    These values have not been changed

    Size of Distribution DB - 24.6GB

     

    We do a large no of transactions, but i'm not sure that the clean up should take so long and the blocking is ridiculous. 

    Changing the settings for storing the transactions shouldn't make too much difference should it? As it keeps them until they are commited to the subscriber anyway?

    Thanks

     

  • November 8, 2004 at 6:56 am

    #529257

    Transactions are kept until they are all applied at the subscribers. Is means that if you transactions aren't applied within the time that it is specified, then they will be marked for reinitialization.

     

    Did you or someone changed that value recently? Could be that before it has a big value? for example 100 days, or something like that?

  • November 8, 2004 at 7:39 am

    #529270

     To my knowledge, nothing has been changed within these settings.  I think 120 hours is ample time, if things hadn't been applied by then i would have problems anyway! 

    Even if this is the case and a setting has been changed, one successful run of the clean up would enable it to catch up the difference and it would run through quickly again? It runs ok, just takes hours and blocks.

    Is there a way to optimize the distribution database?  I don't back it up or optimize it in any way at the moment?

     

    Thanks

     

     

     

  • November 8, 2004 at 7:46 am

    #529271

    I Think that if you have a distribution db that big, is because in sometime, someone decided to save many days of replicated transactions.

     

    You could try changing the retention time, to a higher value, and running again the clean up agents. Once it finished, change again the value to a bit less, and so on, until you reach the 120 hours o retention.

     

    The size of the distribution db, should also be smalller.

  • November 8, 2004 at 7:50 am

    #529272

    Will give it a try, thanks for your time

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply