Post reply

Replication, what are different agents and how and where to run, start, stop, restart them?

  • November 10, 2011 at 4:45 am

    #390825

    Hi,

    SQL 2005 Standard SP4.

    Getting problem with replication and i sometimes get confused with the message and to find which agent is where and how to run.

    Getting following error for identity ranges

    "for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent."

    replication i am using is "Transaction replication with updatable subscriber"

    I have already executed "sp_adjustpublisheridentityrange" on publisher.

    Now where and how to run this "run the Distribution Agent or the Merge Agent" on Subscriber. Is this "Repl-Distribution" Job? which i have tried already.

    Can you please help to identify

    how to start/stop each agent?

    which job or job category is linked with which agent?

    Thanks.

  • November 10, 2011 at 4:56 am

    #1405634

    You can go to SQL ServeR Agent -> Jobs-> Job Activity monitor-> and sort the jobs by 'category'

    you would find all your replication related jobs.

    M&M

  • November 10, 2011 at 5:47 am

    #1405667

    Please confirm again

    "How to run the Distribution Agent or the Merge Agent"?

    Thanks.

  • November 10, 2011 at 6:01 am

    #1405678

    Ok Now to run the Distribution Agent again, as i understood for BOL

    that i stopped and restarted "Repl-Distribution" category job

    this job has 3 steps

    1. Distribution agent startup message

    2. run agent

    3. Detect nonlogged agent shutdown

    its running since quite a while and its still on step 2. Will this ever finish or suppose to finish or will this ever suppose to reach to step 3 and finish?

    I have tested all things on publisher and subscriber but i am not getting any solution of the following problem.

    "If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent."

    Any idea?

    Thanks.

  • November 10, 2011 at 6:29 am

    #1405698

    its running since quite a while and its still on step 2. Will this ever finish or suppose to finish or will this ever suppose to reach to step 3 and finish?

    Don't worry about this. This is how the distribution agent would have been configured (made to run continously) when replication was configured on this server.

    I have tested all things on publisher and subscriber but i am not getting any solution of the following problem.

    "If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent."

    Can you post the full error message. you could find more info from the replication monitor

    M&M

  • November 10, 2011 at 6:43 am

    #1405711

    Relication Type is "Transactional Replication With Updatable subscriber"

    Thats the problem that i am not getting any error in the Replication Monitor however i picked the following error from profiler.

    The insert failed. It conflicted with an identity range check constraint in database 'MyDatabase', replicated table 'dbo.my_Table', column 'key_code'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

    This only happens when i run same procedure on subscriber. If i do other way round i.e. publisher to subscriber then it works and it gets replicated as well.

    So i have executed "sp_adjustpublisheridentityrange" on publisher database many times.

    In replication monitor, in subscriber detailed view, i have "Stop Disbribution Agent" and then "Start Distribution agent"

    But still i am not getting rid of this error.

    Any other reason or idea please?

    Thanks.

  • November 10, 2011 at 7:33 am

    #1405745

    I came across this link, can you just check if this is applicable to you. This refers to merge replication but I guess this is worth a look.

    M&M

  • November 10, 2011 at 10:01 am

    #1405902

    Well the problem was due Active transactions in "log_reuse_wait_desc"

    select name, log_reuse_wait_desc

    from

    sys.databases

    Closed the long running transaction, and then try other fix options.

    If possible then drop and re-created the replication.

    Thanks for your help

    Cheers.

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

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