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 :
- Publication Status
- Subscription Status
- Send Email by the Current status
Second Post :
- Replication information for the Complete setup.
- 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.
DB Cloud TECH 