none
Transnational Replication not continuing after the subscriber Database Restart RRS feed

  • Question

  • My transnational replication is connected to 8 branches from a head office database. It is continuously running subscription from HO to Branch. But it is not continuing after if any branch database is restarted. it showing error message like

    "Agent '' is retrying after an error. retries attempted. See agent job history in the Jobs folder for more details."

    but if we try to connect that database from HO is possible, as well as if we restart the agent in Head office it will work.

    Please give me a solution


    Tuesday, December 6, 2016 12:59 PM

Answers

  • In that case, I would highly suggest you change your subscription from "continuous" to every 1 minute.  After the change you could also change the start and end time of the job to exclude the time you know the subscriber server is unavailable to avoid the error messages.

    See:

    http://www.theboreddba.com/Categories/replication/Continuous-or_scheduled-replication.aspx

    Thursday, December 8, 2016 1:20 PM

All replies

  • Hi Abhilash,

    I will suggest to enable verbose logging for replication distribution agent (Job) which is described in below KB article.

    https://support.microsoft.com/en-us/kb/312292

    This will help to pin point exact error happening in the background. You will probably see distribution agent continuously running and retrying mode because by default distribution agent job is created with almost infinite retry mode.


    Kindly mark the reply as answer if they help

    Tuesday, December 6, 2016 1:04 PM
  • Can you run the agent from the command prompt to see what it gives? Extract the command from the job step and run it from the command prompt here:

    C:\Program Files\Microsoft SQL Server\120\COM

    Adjusting for your version.

    For me - I would run this:

    C:\Program Files\Microsoft SQL Server\120\COM\Distrib.exe -Subscriber [PUBLISHER] -SubscriberDB [Trace_10_25_2016] -Publisher [PUBLISHER] -Distributor [PUBLISHER] -DistributorSecurityMode 1 -Publication [xxxa] -PublisherDB [Agile]   

    Tuesday, December 6, 2016 1:05 PM
    Moderator
  • Thanks Sunil,

    I appreciated your quick reply.

    I have added the verbose logging. and I am waiting to happens the same error on tomorrow morning. I mean after the client has shutdown the system on night.

    Tuesday, December 6, 2016 1:29 PM
  • What do you mean "if we restart the agent in Head office it will work."?  Are you using PUSH subscriptions? 

    I have seen in some cases continuous replication job cannot recover for certain errors.  I always change the jobs to run every 1 minute.  That works much better and creates a log you can read when it has an error.

    Tuesday, December 6, 2016 2:38 PM
  • Yes Mr.TOM

    I am using push subscription from Head office database to branch database. But Every night the branch computer will be shutdown. then the next morning they will switch own the Computer.  While that time the push subscription not changing from error to  live.

    Thursday, December 8, 2016 5:24 AM
  • In that case, I would highly suggest you change your subscription from "continuous" to every 1 minute.  After the change you could also change the start and end time of the job to exclude the time you know the subscriber server is unavailable to avoid the error messages.

    See:

    http://www.theboreddba.com/Categories/replication/Continuous-or_scheduled-replication.aspx

    Thursday, December 8, 2016 1:20 PM
  • By making the job continuous or scheduled the job will try for 2147483647 times before either failing or running after the next schedule. It is possible that the error condition will have cleared by this time, it is also possible that the job will continue to fail until the subscription is expired. To be as proactive as possible you should react to errors which are not transient - like the one you have apparently bumped into to.

    Your central point of diagnosis should be the msrepl_errors table in the distribution database where your error message should be logged. Check here to see what the actual error message is.

    Thursday, December 8, 2016 2:29 PM
    Moderator