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.

Replication in SQL Server Series  

1     2      3       4 

DMV Replication Monitoring Series  1  2  3

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

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

[sql]

USE [MSDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[replDistributorStatusGet]
@pRecipients varchar(255) = 'SQLGULF-DBConsultant@outlook.com'
AS
begin
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(4000)
DECLARE @categoryid int

SELECT @job_owner = SUSER_SNAME()
 ,@is_sysadmin = 1
 ,@running = 0
 ,@categoryid = 10

CREATE TABLE #jobStatus (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 #jobStatus
 EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner--, @job_id

--
select j.name, js.command, jss.running
from msdb.dbo.sysjobsteps js
 join msdb.dbo.sysjobs j on js.job_id = j.job_id
 join #jobStatus jss on js.job_id = jss.job_id
where step_id = 1 and subsystem = 'Distribution'
 and command like '%-Continuous'
 and jss.running <> 1
 if @@ROWCOUNT > 0
 BEGIN

 SELECT @msg_header = 'Distributor job(s) FAILING OR STOPPED. Please check replication job(s) ASAP.'
 SELECT @msg_header = @msg_header + char(10)
 SELECT @msg_header = @msg_header + 'Here is the list of Job(s) that are failing or stopped'
 SELECT @msg_header = @msg_header + char(10)
 SELECT @msg_header = @msg_header + '******************************************************************'

 set @msg = ''
 select @msg = @msg + CHAR(10) + j.name
 from msdb.dbo.sysjobsteps js
 join msdb.dbo.sysjobs j on js.job_id = j.job_id
 join #jobStatus jss on js.job_id = jss.job_id
 where step_id = 1 and subsystem = 'Distribution'
 and command like '%-Continuous'
 and jss.running <> 1

 SELECT @msg = @msg_header + char(10) + nullif(@msg,'')

 print @msg

 exec msdb.dbo.sp_send_dbmail
 @recipients= @pRecipients ,
 @subject= 'Production Replication Distributor Alert',
 @body = @msg
 END
drop table #jobStatus
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].[replDistributorStatusGet]
 @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.