Monitor SQL replication subscriptions synchronization status

In your current role if you are managing a SQL server replication setup, you are probably well aware that if any of the subscriber is not getting synchronized within a reasonable time (which is subjective and variable, I may add), it has serious consequences not only for the subscribers but also for the publisher.

So I had written this little script to see if any of the subscriber had not been synchronized in last @hrs. I used 24 hours as a threshold as some of the subscriptions we only synchronized after hours. So adjust that value according to your needs.

Caveat: I wrote this script long time ago to monitor and get alert if any of the replication subscribers were falling behind or had completely stopped getting synchronized with the publisher. So its not up to date with any new features etc. that newer versions of SQL Server may have added to the replication technology. For example there maybe now built in alerts you could leverage to achieve the same results. The built alerts were probably available even back then as well but I had found my little custom script much more suitable for our needs at that time. So I am hoping you may find this just as useful or give a starting point for your own solution.

RUN THIS SCRIPT ON THE DISTRIBUTOR SERVER

/* Execute this script on the distributor server
Description: Return subscriptions that have not been synchronized in last @hrs hours.

The script loops through each publisher served by the local distributor server.

*/
SET NOCOUNT ON
-- declare variables
DECLARE @SQL NVARCHAR(4000), @SERVERNAME VARCHAR(500), @mypublisher nvarchar(500), @hrs int
DECLARE @rcpts  nvarchar(1000), @msg  nvarchar(1000), @sub nvarchar(150), @query2 nvarchar(2000)

SET @hrs = 24 -- return subscription that has not synchronized in last @hrs
SET @rcpts = '<YOUR EMAIL ADDRESS>'


-- make sure the distribution server is configured for the data access 
if not exists (select * from sysservers where srvid = 0 and dataaccess = 1)
	EXEC('exec sp_serveroption @server = ''' + @@SERVERNAME + ''', @optname = ''DATA ACCESS'', @optvalue = ''TRUE''')

-- uses a cursor to loop through each publisher
DECLARE C1 CURSOR FOR SELECT NAME FROM MSDB..MSdistpublishers ORDER BY 1
OPEN C1
FETCH C1 INTO @mypublisher

WHILE @@FETCH_STATUS = 0
BEGIN
	IF OBJECT_ID('TEMPDB..##Temp_sp_replmonitorhelpsubscription') IS NOT NULL
	   DROP TABLE ##Temp_sp_replmonitorhelpsubscription

	SET @SQL = 'select * INTO ##Temp_sp_replmonitorhelpsubscription from openquery([' + @@SERVERNAME  + '], ''SET FMTONLY OFF exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = ''''' + @mypublisher + ''''', @publication_type = 0, @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N''''0'''''')'

	PRINT @SQL
	EXEC (@SQL)
	IF EXISTS (SELECT @mypublisher Publisher, * FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs)
	BEGIN
		SELECT @mypublisher Publisher, * FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs
--		SELECT @mypublisher Publisher,Subscriber, Publication FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs
		SET @sub = @mypublisher + ': Subscribers found with no data sync in last ' + cast(@hrs as varchar(15)) + ' hours'
		SET @msg = ''
		SET @query2 = 'SET NOCOUNT ON; SELECT ''' + LEFT(@mypublisher, 25) + ''' Publisher,LEFT(Subscriber, 15) Subscriber, LEFT(Publication, 35) Publication, last_distsync [LastDistSync] FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > ' + cast(@hrs as varchar(15)) 
		EXEC dba..sp_sendmail 
			@recipients = @rcpts  
			,@message = @msg  
			,@subject = @sub  
--			,@body_format = 'HTML'
			,@query = @query2  

		
	END	
	FETCH C1 INTO @mypublisher
END

CLOSE C1
DEALLOCATE C1

 .