Problem
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.
Solution
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
AS
BEGIN
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 )
begin
declare @subject nvarchar(100)
select @subject = N'Replication Agents Status on '+@@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = N'email id',
@subject = @subject,
@body = 'One or more agents found stopped'
end
*/
drop table #JobList,#xp_results
END
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.
Ranga Babu is a SQL Server DBA with experience in handling large Development, QA and Production database environments. I have worked on SQL Server high availability solutions like log shipping, replication, mirroring and clustering. I always like to work on SQL Server performance tuning and automation projects.