CODEWITHSUNDEEP
sql-serverreplicationsql-agent-jobtransactional-replication
user25935915
user25935915

Reputation: 3

Send Email When the Distribution Agent or Log Reader Agent STOPS (intentionally or unintentionally)

I want to send an email to the operators when the distribution agent or Log Reader SQL jobs STOP

I have transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. This is a push subscription.

Can someone please suggest how can I add another step to the Distributor Agent SQL job to send an email to operators when the job is stopped? I can use sp_send_dbmail in the steps and use the following script to check if there exists any job activity which has stopped:

IF EXISTS(SELECT 1
          FROM msdb.dbo.sysjobs J 
          JOIN msdb.dbo.sysjobactivity A  ON A.job_id=J.job_id 
          WHERE J.name=N'PubServer-publicationName-SubscriberServer-24' 
          AND A.run_requested_date IS NOT NULL 
          AND A.stop_execution_date IS NOT NULL

         )
         Begin 

         EXEC sp_send_DbMail
         @recipients = '[email protected]',
         @Subject = 'The Distibution Agnet is failing',
         @body = 'Body of the email'

         End

Now - the question is what changes should I make to the 'On Success' and 'On Failure' of the previous job step and what should be the 'On Success' and 'On Failure' of this new job steps:

And is my approach to send email notification when the distribution agent STOPS correct?

I have looked up the internet and only been able to find solutions for when the SQL agent job fails - I know how to add such notifications when the job fails. But I want to send email when the job stops.

Upvotes: 0

Views: 41

Answers (0)

Related Questions