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
.