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.