none
SQL Replication - Agent Jobs were missing for the subscriber RRS feed

  • Question

  • Hi,

    I'm having a SQL Server Replication running with a publisher and distributor in the same server with 7 other pull subscriptions in different physical locations. I had to a face a critical issue few times before and now again. Noticed that my data are not synchronizing between servers and I checked subscriber with "View Synchronization Status" and got the following error which says agent job not configured for the pull subscription.

    then I checked for the Agent Jobs and was shocked to see all my jobs were GONE!!!... Just gone.. missing...

    Experienced this for few times after creating my missing jobs with a working server's script.

    Does anyone have a clue what is happening behind this and how could I overcome with this headache.

    Thanks in advance!!!

     - Chama -

    Monday, November 11, 2019 9:40 AM

All replies

  • Hi,

    Another user had a similar issue here but for merge replication.  You can use the same steps there but use sp_addpullsubscription _agent rather than sp_addmergepullsubscription_agent.

    So:

    exec sp_addpullsubscription_agent 
     @publisher = 'yourpublisherservername', 
     @publisher_db = 'yourpublisherdb', 
     @publication = 'yourpublicationname', 
     @distributor = 'yourdistributorservername'
    This will recreate the agent for you without reinitializing anything, but the steps you created will go away

    Also from that link: "This happens everytime you add a step job and I believe it's a bug in sql server."

    Hope that helps?

    Thanks,

    Matt

    Monday, November 11, 2019 11:02 PM
  • Hi Matt,

    Thanks for the reply. I also managed to re-create the job with the script.

    Is there any way to overcome from this issue since it's a headache to re-create all the jobs when they are missing.

    What could be the reason behind this? Why Agent jobs are just missing?

    Thanks again.

    Chama

    Thursday, November 14, 2019 6:39 AM
  • Hi Chama,

    As this seems a big in SQL, I would advise upgrading it to the latest version, which you can find here.

    But please test any updates before applying to production.

    Thanks,

    Matt

    Saturday, November 16, 2019 7:33 PM