Hello my followers  in the previous post i published part two in the series of (SQL Server DMV Replication Monitoring) and i explained very interested point for any DBA working on replication solutions by simple DMV depend on dynamic Query technique and on SQL Server cursor technology.

Summary of the previous posts

First Post :

1. Publication Status
2. Subscription Status
3. Send Email by the Current status

Second Post :

1. Replication information for the Complete setup.
2. Find publication article.

Third Post :

How we can Check Distributor AGENT.

Replication in SQL Server Series  1 2 3 4 

DMV Replication Monitoring Series  1 2 3 4

Today i will Explain one new point it is very helpful and attractive , How we can Check LogReader AGENT Status

by the below Stored procedure we can Execute it from SQL Server AGENT to send to us notification mail if the LogReader JOB(s) FAILING OR STOPPED.

SOURCE POST : WWW.MostafaElmasry.WordPress.Com

[sql]

USE [MSDB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[replLogReaderStatusGet] 
@pRecipients varchar(255) = 'SQLGULF-dbconsultant@outlook.com' 
AS 

 
 SET NOCOUNT ON; 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
 DECLARE @is_sysadmin INT 
 DECLARE @job_owner sysname 
 DECLARE @job_id uniqueidentifier 
 DECLARE @job_name sysname 
 DECLARE @running int 
 DECLARE @cnt int 
 DECLARE @msg varchar(8000) 
 DECLARE @msg_header varchar(1000) 
 DECLARE @categoryid int 
 
 
select la.name,la.publisher_db, 
 case lh.runstatus 
 when 1 then 'Start' 
 when 2 then 'Succeed' 
 when 3 then 'In progress' 
 when 4 then 'Idle' 
 when 5 then 'Retry' 
 when 6 then 'Fail' 
 else 'Unknown' 
 end as runstatus 
 , lh.time, lh.comments 
from distribution..MSlogreader_history lh 
 inner join distribution..MSlogreader_agents la on lh.agent_id = la.id 
 inner join ( 
 select lh.agent_id, max(lh.time) as LastTime 
 from distribution..MSlogreader_history lh 
 inner join distribution..MSlogreader_agents la on lh.agent_id = la.id 
 group by lh.agent_id) r 
 on r.agent_id = lh.agent_id 
 and r.LastTime = lh.time 
where lh.runstatus not in (3,4) 
 
if @@rowcount > 0 
 
BEGIN 
 SELECT @job_owner = SUSER_SNAME() 
 ,@is_sysadmin = 1 
 ,@running = 0 
 ,@categoryid = 13 
 
 CREATE TABLE #job (job_id UNIQUEIDENTIFIER NOT NULL, 
 last_run_date INT , 
 last_run_time INT , 
 next_run_date INT , 
 next_run_time INT , 
 next_run_schedule_id INT , 
 requested_to_run INT , 
 request_source INT , 
 request_source_id sysname COLLATE database_default NULL, 
 running int , 
 current_step INT , 
 current_retry_attempt INT , 
 job_state INT) 
 
 INSERT INTO #job 
 EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner 
 
 SELECT @running = isnull(sum(j.running),-1),@cnt = count(*) 
 FROM #job j 
 join msdb..sysjobs s on j.job_id = s.job_id 
 where category_id = @categoryid 
 
 if @running <> @cnt 
 BEGIN 
 SELECT @msg_header = 'LogReader job(s) FAILING OR STOPPED. Please check replication job(s) ASAP.' 
 SELECT @msg_header = @msg_header + char(10) 
 SELECT @msg_header = @msg_header + '**************************************************************' 

 set @msg = '' 
 SELECT @msg = @msg + char(10)+'"' + s.[name] + '" - '+ convert(varchar, isnull(j.running,-1)) 
 FROM #job j 
 join msdb..sysjobs s on j.job_id = s.job_id 
 where category_id = @categoryid 
 and isnull(j.running,-1) <> 1 
 
 SELECT @msg = @msg_header + char(10) + nullif(@msg,'') 
 

 exec msdb.dbo.sp_send_dbmail 
 @recipients= @pRecipients , 
 @subject= 'Production Replication LogReader Alert', 
 @body = @msg 
END 
END 
GO
[/sql]

Now create new SQL Server agent job and execute this Stored procedure on it

sql]
USE [msdb]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[replLogReaderStatusGet]
@pRecipients = N'SQLGULF-dbconsultant@outlook.com'
SELECT 'Return Value' = @return_value
GO
[/sql]

Follow me to know more SQL Server DMV in Replication Monitoring

Follow  the Author :

View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.