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.
Replication in SQL Server Series
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.
from WordPress about my Community activity from HERE , and you can check my first arabic post in SQL Server 2014 from HERE who love reading can check my technical document in SQL Server 2012 , 2014 Technology from HERE and at the end i am still new SQL Server Speaker i published four technical videos in SQL Server technology you can check it from HERE
DB Cloud TECH 