Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Simple script to check SQL Server Replication Jobs

By:   |   Updated: 2017-01-20   |   Comments (3)   |   Related: More > Replication


We have SQL Server Replication configured on our servers and every once in a while we find the SQL Server Agent Jobs replication are stopped and data is not being replicated.  We can find the status of the replication agents either using replication monitor or the SQL Agent Job Activity, but it takes time to login into each server to check the status.


To automate checking the replication jobs, I created the following stored procedure.  This can be setup as a SQL Server Agent Job to periodically check and alert you if there are issues.  The procedure checks the status of SQL Server Agent Jobs where the job category is one of the following: REPL-Merge, REPL-Distribution, REPL-LogReader and if any are not running it sends an email alert.

I commented out the section that sends email, but you can remove the comments and put in the necessary parameters for your environment.

Here is the stored procedure.

CREATE PROCEDURE GetReplicationAgentStatus

set nocount on
set transaction isolation level read uncommitted 

Make sure your agents are in the correct category 
i.e Merge agents under REPL-Merge, 
Distribution agents under REPL-Distribution 
and LogReader agent under REPL-LogReader

select s.job_id,s.name,s.enabled,c.name as categoryname into #JobList 
from msdb.dbo.sysjobs s inner join msdb.dbo.syscategories c on s.category_id = c.category_id
where c.name in ('REPL-Merge','REPL-Distribution','REPL-LogReader')

create TABLE #xp_results  
   (job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL, 
    request_source        INT              NOT NULL,
    request_source_id     sysname          COLLATE database_default NULL,
    running               INT              NOT NULL,
    current_step          INT              NOT NULL,
    current_retry_attempt INT              NOT NULL,
    job_state             INT              NOT NULL)

insert into #xp_results 
exec master.dbo.xp_sqlagent_enum_jobs 1, ''

select j.name,j.categoryname,j.enabled, AgentStatus = CASE WHEN r.running =1 THEN 'Running' else 'Stopped'   end
from #JobList j inner join #xp_results r on j.job_id=r.job_id

-- Uncomment the below portion and use correct parameters to send email alert
if exists (select j.name,j.categoryname,j.enabled,r.running
from #JobList j inner join #xp_results r   on j.job_id=r.job_id where running =0 )
   declare @subject nvarchar(100)
   select @subject = N'Replication Agents Status on '[email protected]@servername

   EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'ProfileName',
      @recipients = N'email id',
      @subject = @subject,
      @body = 'One or more agents found stopped'
drop table #JobList,#xp_results

Next Steps

  • Setup this process to check replication, so you can be alerted if the replication jobs are not running.
  • Read these additional tips about Replication.

Last Updated: 2017-01-20

get scripts

next tip button

About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, February 23, 2018 - 12:50:51 PM - Rob Back To Top

 nice! thanks.you can also do the same with database mail..Create an operator, and right click the  job in sql agent jobs, select notification, select notify operator ( the one you just created) , and select 'notify when the job fails' from the dropdown list.

you'll be notified everytime that job fails.


Sunday, March 12, 2017 - 11:49:36 PM - Vivek Back To Top

Hello Ranga Babu,


Per my review looks like you worked a lot in the Replication space. Curious if you can share your contact info so I can contact you to discuss some of our challenges specific to a project / implementation - which I cannot share in the public space.


Thank you in advance,


[email protected]


Thursday, February 02, 2017 - 6:08:44 AM - Yelender Merugu Back To Top

Hi ranga,

Thank you for posting the valuable information.  I was looking for this since long. Please keep on posting such type of posts. 


Learn more about SQL Server tools