SQL Server Performance

Auto-start replication after Network error

Discussion in 'Performance Tuning for SQL Server Replication' started by anrovi, Sep 6, 2006.

  1. anrovi New Member

    We have two servers with merge replication and five agents. Once a week, our link fails 1 or 2 minutes and one agent fails with this message:

    The merge process encountered an unexpected network error. The connection to Subscriber 'SERVER2' is no longer available.

    When I detect that agent is not running due to an error replication network error, I have to start agent manually.

    How can I do to SQL agent try replicate after a Network error ???
  2. satya Moderator

    It is suspected that this problem is caused by the orphaned process. Please<br />try the following steps and check to see if it able to fix your problem.<br /><br />One of the technet article refers:<br />1. In Enterprise Manager, under "Replication Monitor", Go to "Agents" folder, Look for your failing Agent there. Right Click and Stop the agent if it is in a retrying status.<br /><br />2. Under Enterprise Manager, under "Replication Monitor", Go to "Agents" folder, right click on the failing agent, choose Agent, properties. From the Agent properties dialog box, copy the name of the agent.<br /><br />3. In the Query Analyzer, modify Your_Agent_Name with the name from the previous step in the following command and execute<img src='/community/emoticons/emotion-7.gif' alt=':S' />elect spid from sysprocesses where program_name = 'Your_Agent_Name'<br /><br />4. Obtain the spid from the output, and use the Kill command to kill that<br />orphan process: Kill spid<br /><br />5. Restart the applicable agent.<br /><br />May try above to get it work.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  3. anrovi New Member

    I have same problem with merge replication. Today, subscription server restart and publication agents stops with error.

    Sometime I read about -Continuous parameter and I wrote this to run agent:

    -Publisher [PUBLISHERSERVER] -PublisherDB [MYDATABASE] -Publication [ARTICLE] -Subscriber [SUBSCRIPTIONSERVER] -SubscriberDB [MYDATABASE] -Distributor [PUBLISHERSERVER] -DistributorSecurityMode 1 -Continuous

    How can I configure agent to retry forever and ever ???

  4. satya Moderator

    Have you performed the refered steps above?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. anrovi New Member

    Hi Satya!

    Today we found all agents in stop status due to a network error again. Before starting them manually, we searched again for their spid but query no return information.

    Have you another idea about this issue?

    Thanks a lot !
  6. satya Moderator

    Just another thought can you check whether MDAC version is similar on both the machines?
    Also the SQL service pack level.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  7. anrovi New Member

    Both servers are identically (hardware and software).

Share This Page