SQL Server Performance

Distribution Agent do not work

Discussion in 'Performance Tuning for SQL Server Replication' started by tuankm, Nov 24, 2004.

  1. tuankm New Member

    Hi all, Please help me to solve the following problem:

    I set up a transactional replication between Server A and Server B with Subscription expiration opiton: Subscriptions expire and are dropped if not synchoronized on the following number of hours: 336 . Server A is publisher and distributor, Server B is subscriber. It's OK.

    But yesterday, the network connection between two Servers is disconnected. And Distribution Agent in Ditributor Server do not work. Therfore the replication is not OK.

  2. Argyle New Member

    What is the error message from the distribution agent?

    You can always try and resync the replucation by running the snapshot agent and then the distribution agent.

    If it's been a long time since it failed the publication might be set to inactive. In that case start the log reader job again if it has stopped and run the following script in the publication database:

    sp_changepublication 'MyPublication', @property='status', @value='active'

    where MyPublication is your publication name.

    After that run the snaphost agent and when it's done run the distribution agent to transfer the data to the subscribers.

  3. tuankm New Member

    Thanks for your reply

    After disconnected for 5 days, the network connection is connected again, but the distribution agent do not work. Can you tell me how the distribution agent automaticlly work when the network connection is connected.

    Thanks
  4. Twan New Member


    The agent won't automatically work if they were set to continuously replicate, you'd have to start the distribution job. The reason being that with continuous replication the job would normally not stop, but since the nw disconnected the job fails and so needs to be started again.

    Cheers
    Twan
  5. satya Moderator

    Check the privileges for the SQLAgent service account on both the servers and refer to SQLAgent log for further information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. tuankm New Member

    quote:Originally posted by Twan


    The agent won't automatically work if they were set to continuously replicate, you'd have to start the distribution job. The reason being that with continuous replication the job would normally not stop, but since the nw disconnected the job fails and so needs to be started again.

    Cheers
    Twan

    Thanks Twan, but I want to know how to set up a replication without a continuous property.
  7. satya Moderator

    That can be controlled using scheduled jobs on the SQL Server, you can set the jobs to execute manually without any specific schedule.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page