[Replication Monitor] YA monitor - check delay times with last_distsync and sp_replmonitorhelpsubscription

Got hosed because I accidentally added a a clause in our "how backed up are we" from http://thebakingdba.blogspot.com/2010/10/replication-better-alternative-to.html , and set it up wrong, so I filtered stuff I didn't mean to.

Lessons learned, and got me thinking ... One thing I haven't had on my replication monitor was a reliable way of detecting if any of my subscriptions are expired or about to expire.  The dread 72 hours.  So, several hours later, a profile trace and a question on StackOverflow, and I got a way to do it.

Basically, you're running the system SP sp_replmonitorhelpsubscription, once for each server that the distributor handles.  You then filter that out (looking for warnings and ignoring recent records) and send an email if there's anything left.

One downside: because we're trying to avoid the NESTED EXEC issue (can't have an INSERT INTO EXEC where the code you're running has an INSERT INTO EXEC), the most reliable way to avoid it is by using OPENROWSET (thanks to Paul Ibison for that).  However, that requires that Ad Hoc Distributed Queries be enabled via sp_configure.  Yes, it can be a security hole.

An alternative way to do it, according to replication expert Hilary Cotter, is to run the SP twice, and only INSERT INTO on the second Exec.  In my testing it's not as reliable (fails occasionally, and doesn't lend itself to automation quite as well, but it may be a better option for you.

Hope this helps (and hopefully the blogspot sourcecode formatter I'm using is good)!

	--2013/06/17 1.10 mdb thebakingdba.blogspot.com
	-- added filter for "last_distsync is null", which should only be running or never-run.
	DECLARE @min INT, @max INT, @sql NVARCHAR(4000)
	DECLARE @repl_server_list TABLE(id INT IDENTITY, srvname sysname)
	SET NOCOUNT ON
	--build a stripped down temp table; the OPENROWSET allows us to skip fields we don't care about.
	IF OBJECT_ID('tempdb..#tmp_subscriptiondata') IS NOT NULL
	DROP TABLE #tmp_subscriptiondata
	create table #tmp_subscriptiondata (
	[status] int null,
	warning int null ,
	subscriber sysname null ,
	subscriber_db sysname null ,
	publisher_db sysname null ,
	publication sysname null ,
	publication_type int null ,
	subtype int null ,
	latency int null ,
	latencythreshold int null ,
	agentnotrunning int null ,
	agentnotrunningthreshold int null ,
	timetoexpiration int null ,
	expirationthreshold int null ,
	last_distsync datetime null ,
	distribution_agentname sysname null ,
	mergeagentname sysname null ,
	mergesubscriptionfriendlyname sysname null ,
	mergeagentlocation sysname null ,
	mergeconnectiontype int null ,
	mergePerformance int null ,
	mergerunspeed float null ,
	mergerunduration int null ,
	monitorranking int null ,
	distributionagentjobid binary(30) null ,
	mergeagentjobid binary(30) null ,
	distributionagentid int null ,
	distributionagentprofileid int null ,
	mergeagentid int null ,
	mergeagentprofileid int null ,
	logreaderagentname sysname null
	)
	--list every server that our current server is handling distribution duties for.
	-- we do this since you can tell a different server to be the distributor.
	INSERT INTO @repl_server_list
	SELECT DISTINCT srvname --b.srvname,a.publisher_db,a.publication
	FROM distribution.dbo.MSpublications a, master.dbo.sysservers b
	WHERE a.publisher_id=b.srvid
	--------------------------------
	--Get list of all replications--
	--------------------------------
	SELECT @min = MIN(id), @max = MAX(id) FROM @repl_server_list
	WHILE @min <= @max
	BEGIN
	--Transactional Replication
	select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
	+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=0'')a'
	FROM @repl_server_list WHERE id = @min
	Insert Into #tmp_subscriptiondata
	EXEC sp_executesql @sql
	--Snapshot Replication
	select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
	+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=1'')a'
	FROM @repl_server_list WHERE id = @min
	Insert Into #tmp_subscriptiondata
	EXEC sp_executesql @sql
	--Merge Replication
	select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
	+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=2'')a'
	FROM @repl_server_list WHERE id = @min
	Insert Into #tmp_subscriptiondata
	EXEC sp_executesql @sql
	SET @min = @min + 1
	END
	-------------------
	--Reporting Email--
	-------------------
	--variables and tablevar defined here to more easily add/modify/test rules
	DECLARE @tableHTML NVARCHAR(MAX)
	,@MailSubject VARCHAR(100)
	, @rowcount INT
	DECLARE @final_error_list TABLE (
	subscriber sysname null ,
	subscriber_db sysname null ,
	publisher_db sysname null ,
	publication sysname null ,
	warning int null ,
	last_distsync datetime null ,
	hours_delayed INT,
	distribution_agentname sysname null
	)
	SELECT @MailSubject = '[Replication] Delays/Errors on ' + @@servername
	--using an interim table so that we can query it to see how many they are;
	-- we could use a CTE but then we have no easy way, short of checking the HTML length,
	-- of verifying there are records that need to be emailed.
	INSERT INTO @final_error_list
	SELECT subscriber, subscriber_db, publisher_db, publication, warning, last_distsync,
	DATEDIFF(hh,last_distsync, GETDATE()) AS Hours_Delayed,distribution_agentname
	FROM #tmp_subscriptiondata WHERE warning > 0
	UNION ALL
	SELECT subscriber, subscriber_db, publisher_db, publication, warning, last_distsync,
	DATEDIFF(hh,last_distsync, GETDATE()) AS Hours_Delayed, distribution_agentname
	FROM #tmp_subscriptiondata
	--rule 1 - ignore publications that are current in the last hour or currently running.
	DELETE FROM @final_error_list
	WHERE last_distsync > DATEADD(mi,-60, GETDATE()) OR last_distsync IS null
	--rule 2 - ignore subscriptions that only run once a day, after midnight
	DELETE FROM @final_error_list
	WHERE (publication = 'dailypub' AND last_distsync > CONVERT(CHAR(8),GETDATE(),112))
	--SELECT * FROM @final_error_list
	IF (SELECT COUNT(*) FROM @final_error_list)>0
	BEGIN
	select @tableHTML = N'<H3>Replication Delays and Errors</H3>'
	+ N'<table border="1">' + N'<tr>'
	+ N'<th>Subscriber</th>' +
	+ N'<th> Subscriber_DB </th>'
	+ N'<th> Publisher_DB </th>'
	+ N'<th> Publication </th>'
	+ N'<th>Warning</th>'
	+ N'<th> Last_Distsync </th>'
	+ N'<th> Hours</th>'
	+ N'<th> Distribution_AgentName</th>'
	+ N'</tr>' + CAST((SELECT td = RTRIM(LTRIM(T.Subscriber))
	,''
	,td = RTRIM(LTRIM(T.Subscriber_DB))
	,''
	,td = RTRIM(LTRIM(T.Publisher_DB))
	,''
	,td = RTRIM(LTRIM(T.Publication))
	,''
	,td = RTRIM(LTRIM(T.Warning))
	,''
	,td = CONVERT(VARCHAR(16), T.Last_Distsync, 120)
	,''
	,td = CONVERT(VARCHAR(3), T.Hours_Delayed)
	,''
	,td = RTRIM(LTRIM(T.Distribution_AgentName))
	FROM @final_error_list T
	ORDER BY T.[Warning] DESC, T.last_distsync ASC
	FOR
	XML PATH('tr')
	,TYPE
	) AS NVARCHAR(MAX)) + N'</table>';
	--PRINT @tableHTML
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'yourprofile', @recipients = 'dev@null.com',
	@subject = @MailSubject, @body = @tableHTML, @body_format = 'HTML';
	END
	DROP TABLE #tmp_subscriptiondata

view raw Replication_Delay_Monitor hosted with ❤ by GitHub