Script to get replication latency and alert in SQL Server by serverku

Recently I had posted for one issue I faced for replication trace token, Could not find the Distributor or the distribution database for the local server. Hope you read and liked it. Today I am writing for replication latency and its alert and I am using one script to post a tracer token which I used same in an earlier post where you can see the script there and details for same. So move on the topic here as how can we get replication latency information and alert for same. Putting in steps,

1. Post tracer tokens : Schedule a script to post a tracer tokens frequently, says 5 minutes, which posts a trace token into the transaction log at the Publisher and begins the process of tracking latency statistics and run this script at publisher,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 

 2. Script : After scheduling above scripts every 5 minutes, which will put latency history in MStracer_history system table in distribution database, following script use to get details for the latency from publisher to distributor and distributor to subscriber which belongs to distribution database,

USE distribution 
GO 

SELECT publisher_db                                                    AS 
       [PublisherDB] 
       , 
       publication 
       AS [Publication], 
       name                                                            AS 
       [Subscriber], 
       subscriber_db                                                   AS 
       [SubscriberDB], 
       RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR(2)), 2) AS 
       [DistToSubLatency], 
       RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2)), 2)  AS 
       [PubToDistLatency], 
       RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)), 2) 
       + ':' 
       + RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2)        AS 
       [TotalLatency] 
FROM   (SELECT DISTINCT msda.publisher_db, 
                        syss.name, 
                        msda.subscriber_db, 
                        msda.publication, 
                        publisher_commit, 
                        distributor_commit, 
                        Datediff(ss, publisher_commit, distributor_commit)    AS 
                               [TimeToPublisher], 
                        subscriber_commit, 
                        Datediff(ss, distributor_commit, subscriber_commit)   AS 
                               [TimeToSubscriber], 
                        Datediff(ss, publisher_commit, distributor_commit) 
                        + Datediff(ss, distributor_commit, subscriber_commit) AS 
                               [TotalTime] 
        FROM   mstracer_history msth 
               INNER JOIN msdistribution_agents msda 
                       ON msth.agent_id = msda.id 
               INNER JOIN sys.servers syss 
                       ON msda.subscriber_id = syss.server_id 
               INNER JOIN mstracer_tokens 
                       ON msth.parent_tracer_id = mstracer_tokens.tracer_id 
        WHERE  subscriber_commit > Dateadd(mi, -5, Getdate())) res 
ORDER  BY [totaltime] DESC 

3. Alert : As we have seen a script to get the latency for replication, herewith I am sharing a script to get alerts for highest latency, In script logic is implies to send an alert only when a publisher to distributor or distributor to subscriber latency exceed 1 minute. You can schedule this script to every 5 minutes as we use condition to scan latency history inserted in the last 5 minutes,

USE distribution 
GO 

DECLARE @subject SYSNAME 
DECLARE @body VARCHAR(4000) 
DECLARE @SubscriberTime VARCHAR(20) 
DECLARE @PublisherTime VARCHAR(20) 
DECLARE @TotalTime VARCHAR(20) 
DECLARE @PublisherDB VARCHAR(20) 
DECLARE @Publication VARCHAR(20) 
DECLARE @Subscriber VARCHAR(20) 
DECLARE @SubscriberDB VARCHAR(20) 

SELECT @PublisherDB = publisher_db, 
       @Publication = publication, 
       @Subscriber = name, 
       @SubscriberDB = subscriber_db, 
       @SubscriberTime = RIGHT('0' + Cast([timetosubscriber]/3600 AS VARCHAR(3)) 
                         , 2) 
                         + ':' 
                         + RIGHT('0' + Cast(([timetosubscriber] % 3600) / 60 AS 
                         VARCHAR 
                         (2)), 2) 
                         + ':' 
                         + RIGHT('0' + Cast(([timetosubscriber] % 60) AS VARCHAR 
                         (2)), 2 
                         ), 
       @PublisherTime = RIGHT('0' + Cast([timetopublisher]/3600 AS VARCHAR(3)), 
                        2) 
                        + ':' 
                        + RIGHT('0' + Cast(([timetopublisher] % 3600) / 60 AS 
                        VARCHAR(2 
                        )), 2) 
                        + ':' 
                        + RIGHT('0' + Cast(([timetopublisher] % 60) AS VARCHAR(2 
                        )), 2), 
       @TotalTime = RIGHT('0' + Cast([totaltime]/3600 AS VARCHAR(3)), 2) 
                    + ':' 
                    + RIGHT('0' + Cast(([totaltime] % 3600) / 60 AS VARCHAR(2)), 
                    2) 
                    + ':' 
                    + RIGHT('0' + Cast(([totaltime] % 60) AS VARCHAR(2)), 2) 
FROM   (SELECT DISTINCT msda.publisher_db, 
                        syss.name, 
                        msda.subscriber_db, 
                        msda.publication, 
                        publisher_commit, 
                        distributor_commit, 
                        Datediff(ss, publisher_commit, distributor_commit)    AS 
                               [TimeToPublisher], 
                        subscriber_commit, 
                        Datediff(ss, distributor_commit, subscriber_commit)   AS 
                               [TimeToSubscriber], 
                        Datediff(ss, publisher_commit, distributor_commit) 
                        + Datediff(ss, distributor_commit, subscriber_commit) AS 
                               [TotalTime] 
        FROM   mstracer_history msth 
               INNER JOIN msdistribution_agents msda 
                       ON msth.agent_id = msda.id 
               INNER JOIN sys.servers syss 
                       ON msda.subscriber_id = syss.server_id 
               INNER JOIN mstracer_tokens 
                       ON msth.parent_tracer_id = mstracer_tokens.tracer_id 
        WHERE  subscriber_commit > Dateadd(mi, -5, Getdate())) res 
WHERE  ( [timetopublisher] > 60 
          OR [timetosubscriber] > 60 ) 
-- Fetch if publisher to distributor or distributor to subscriber latency greater than 60 seconds
ORDER  BY [totaltime] DESC 

SET @subject='Replication Latency Alert' 
SET @body= 
'Replication latency exceeded the highest acceptable replication delay' 
SET @body=@body 
          + 'Publisher to Distributor: ' + @PublisherTime 
          + 'Distributor to Subscriber: '+ @SubscriberTime 
          + 'Total Delay: '+ @TotalTime 
          + 'Publication: '+ @Publication 
          + 'Subscriber: ' + @Subscriber 

IF ( @body 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 

This is the details I wanted to present here to catch up replication latency and hope you may like it.