You are here:Home » SSIS » Script to get transactional replication errors for all subscriptions - SQL Server

Script to get transactional replication errors for all subscriptions - SQL Server

Earlier post had same title just with a little bit difference of giving subscribers and all subscribers, so sharing a script which you can watch transactional replication error for all subscribers and get an alert at the time of error raise. Before going ahead with this post I would like you to read my earlier post and share your thoughts there.
So diverting on this post and again sharing script to get the same information, but it for all subscribers, you can also use the parameter of subscriber in where condition to apply filters.
Use distribution
GO
SELECT ma.publisher_db, 
       ma.publication, 
       ma.subscriber_db, 
       msre.time, 
       msre.error_text 
FROM   msrepl_errors msre 
       INNER JOIN msdistribution_history msh 
               ON ( msre.id = msh.error_id ) 
       INNER JOIN msdistribution_agents ma 
               ON ( ma.id = msh.agent_id ) 
ORDER  BY msre.time DESC 
This is just a script which I want to present here. Now how can watch and keep attention when such any error occurs for transactional replication error in last 5 minutes and get an alert for the same with following script which can be scheduled every 5 minutes or whatever frequency as per filter applied of error time in query and run against a distribution database on distributor server,
USE distribution 
GO

DECLARE @body         VARCHAR(max), 
        @subject      VARCHAR(100), 
        @Publication  VARCHAR(50), 
        @SubscriberDB VARCHAR(50), 
        @ErrorText    VATCHAR(max) 

SELECT @Publication = ma.publication, 
       @SubscriberDB = ma.subscriber_db, 
       @ErrorText = msre.error_text 
FROM   msrepl_errors msre 
       INNER JOIN msdistribution_history msh 
               ON ( msre.id = msh.error_id ) 
       INNER JOIN msdistribution_agents ma 
               ON ( ma.id = msh.agent_id ) 
WHERE  msre.time >= Dateadd(minute, -5, Getdate()) 
-- Capturing for last 5  minutes 
ORDER  BY msre.time DESC 

SET @subject = 'Error captured for ' + @SubscriberDB 
               + ' subscriber database of ' + @Publication 
               + ' publication' 
SET @body = @ErrorText 

-- Sending an email 
IF ( @subject IS NOT NULL ) 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @recipients = 'prajapatipareshm@gmail.com', 
        @subject = @subject, 
        @body = @body, 
        @profile_name = '<Profile name>', 
        @body_format = 'HTML'; 
  END 

Have you scheduled any such script to get an alert and watch for any transaction replication error? Your ideas and thoughts are most welcome.