SQL Server Replication Latency Alert via Email

By:   |   Updated: 2018-11-20   |   Comments (1)   |   Related: > Replication


Problem

How can I send an automated email notification when SQL Server data replication latency between a publisher and subscriber is higher than 60 seconds?

Solution

To monitor SQL Server replication, we can use a Tracer Token. The Tracer Token measures the latency between the Publisher and Distributor and the Distributor to Subscribers. This tip explains briefly about tracer tokens, how they work and how to configure them.

In this tip, I am going show how to send an email notification when latency between the Publisher and Distributor or the Publisher and Subscriber is higher than 60 seconds.

To set this up, we must create a SQL Server Agent Job that executes every fifteen minutes. The SQL Server Agent Job executes a stored procedure which performs the following tasks:

  1. Creates a new tracer token in the publication database using system stored procedure named "sp_posttracertoken".
  2. Populates tracer token IDs using system stored procedure named "sp_helptracertokens" and stores it in #TracerToken table.
  3. Populates a recent tracer token ID from the #TracerToken table and store it in "@LiTokenID" variable.
  4. Passes the tracer token ID, stored in "@LiTokenID" to the sp_helptracertokens procedure and saves the output in #Latency table.
  5. Check the value of latency in "OVERALL_LATENCY" column of #Latency table. If it is higher than 1 minute than it sends an email notification.

Replication Configuration

First, let's review the replication configuration. To do that execute the following query on the publisher database.

SELECT
   DistributionAgent.subscriber_db [Subscriber DB],
   DistributionAgent.publication [PUB Name],
   RIGHT(LEFT(DistributionAgent.NAME, Len(DistributionAgent.NAME) - (Len(DistributionAgent.id) + 1 )),
      Len(LEFT(DistributionAgent.NAME, Len(DistributionAgent.NAME) - (Len(DistributionAgent.id) + 1 ))) - (
      10 + Len(DistributionAgent.publisher_db) + (
      CASE
      WHEN DistributionAgent.publisher_db = 'ALL'
      THEN 1
      ELSE Len(DistributionAgent.publication) + 2 END ) )) [SUBSCRIBER],
   (CASE
      WHEN DistributionAgent.subscription_type = '0' THEN 'Push'
      WHEN DistributionAgent.subscription_type = '1' THEN 'Pull'
      WHEN DistributionAgent.subscription_type = '2' THEN 'Anonymous'
      ELSE Cast(DistributionAgent.subscription_type AS VARCHAR)
      END ) [Subscrition Type],
   DistributionAgent.publisher_db + ' - '
      + Cast(DistributionAgent.publisher_database_id AS VARCHAR) [Publisher Database],
   DistributionAgent.NAME [Publisher - PublisherDB - Publication - SubscriptionName - AgentID]
FROM distribution.dbo.msdistribution_agents DistributionAgent      
WHERE DistributionAgent.subscriber_db <> 'virtual'

This screen shot below is the output of the above query.

sql server replication latency email alert 001

Replication Latency Check - Code Explanation

The following explains the code used to check for replication latency issues.  Further down in the article is a complete code listing you can use.  Also, this example uses the AdventureWorks2014 database.  You will need to update the code you use to reflect the database name you are using for replication.

The following code populates the publication name and stores it in the "@lsPublication" variable. This code block must be executed in the publication database.

DECLARE @lsPublication AS sysname;
SET @lsPublication = (SELECT name FROM AdventureWorks2014..syspublications WHERE name = 'Customer_Publication')

This code creates a tracer token in the publication database.

EXEC AdventureWorks2014.sys.sp_posttracertoken 
  @Publication = @lsPublication,
  @tracer_token_id = @liTokenID OUTPUT;

This INSERT command stores the information of recently created tracer tokens in the temp table.

INSERT #tracer_tokens (tracer_id, publisher_commit)
EXEC AdventureWorks2014.sys.sp_helptracertokens @Publication = @lsPublication;

The following code block stores the recent tracer token ID of in "@LiTokenID" variable.

SET @liTokenID = 
  (
  SELECT TOP 1 tracer_id 
  FROM #tracer_tokens
  ORDER BY publisher_commit DESC
  )
DROP TABLE #tracer_tokens

Below is code that creates a temp table to store the latency results of a tracer token.

CREATE TABLE #Latency 
  (
  DISTRIBUTOR_LATENCY INT, 
  SUBSCRIBER VARCHAR(255),
  SUBSCRIBER_DB VARCHAR(255),
  SUBSCRIBER_LATENCY INT,
  OVERALL_LATENCY INT
  )

Once the table gets created, the following code block populates the latency values of token ID, stored in the "@LiTokenID" variable using the "sp_helptracertokenhistory" stored procedure and inserts it into the #Latency table.

INSERT INTO #Latency (distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency)
EXEC AdventureWorks2014.sys.sp_helptracertokenhistory 
  @Publication = @lsPublication, 
  @tracer_id = @liTokenID;

The following code block populates the default SQL Server Database Mail profile and stores it in the "@ProfileName" variable.

DECLARE @DBMailProfile TABLE (ProfileID INT, ProfileName SYSNAME, [Description] NVARCHAR(4000))
DECLARE @ProfileName SYSNAME
INSERT INTO @DBMailProfile (ProfileID, ProfileName, [Description])
EXEC msdb.dbo.sysmail_help_profile_sp @profile_id = 1
SET @ProfileName = (SELECT ProfileName FROM @DBMailProfile)

The following code block checks the value of the "overall_latency" column of the #Latency table. If the value is higher than 1 minute then an email is sent.

IF (SELECT MAX(ISNULL(OVERALL_LATENCY, 100)) FROM #Latency) >= 59 
  BEGIN
         SET @lsMessage = 
         '<p style="font-family:Arial; font-size:10pt">There is an error in database replication.  ' + 
         'Latency has exceeded 1 minute. This can be caused by a patch, configuration or database error.  ' +
         'Please Login to publisher server, then use the following command to open <b>SQL Replication Monitor</b> to determine the error.' + '<br><br>' + 
         '<table border=1 cellspacing=0 cellpadding=0 bgcolor=#F2F2F2><tr><td><font face="Courier New" size=2>"C:\"Program Files (x86)"\"Microsoft SQL Server"\120\Tools\Binn\SqlMonitor.exe" </font></td></tr></table>' + '<br>' + 
         '<b>NOTE:</b> This alert will be generated every 15 minutes until replication is caught up for the AdventureWorks2014 databases on <b>all</b> subscribers.  ' +
         '</p>'
      EXEC msdb.dbo.sp_send_dbmail  
         @recipients='[email protected]', 
         @body=@lsMessage, 
         @subject='Replication Alert: Higher Latency', 
         @file_attachments='', 
         @copy_recipients='', 
         @blind_copy_recipients='', 
         @importance='high', 
         @body_format='HTML', 
         @profile_name=@ProfileName
  END

Create Replication Latency Check Stored Procedure

The following is the complete set of code for the stored procedure.

CREATE PROCEDURE [dbo].[sp_ReplicationLatencyMonitor] AS
Begin
SET NOCOUNT ON
DECLARE @liTokenID AS int;
DECLARE @lsMessage VARCHAR(2000)

DECLARE @lsPublication AS sysname;
SET @lsPublication = (SELECT name FROM AdventureWorks2014..syspublications WHERE name = 'Customer_Publication')

DECLARE @DBMailProfile TABLE (ProfileID INT, ProfileName SYSNAME, [Description] NVARCHAR(4000))
DECLARE @ProfileName SYSNAME
INSERT INTO @DBMailProfile (ProfileID, ProfileName, [Description])
EXEC msdb.dbo.sysmail_help_profile_sp @profile_id = 1
SET @ProfileName = (SELECT ProfileName FROM @DBMailProfile)

EXEC AdventureWorks2014.sys.sp_posttracertoken 
  @Publication = @lsPublication,
  @tracer_token_id = @liTokenID OUTPUT;

CREATE TABLE #tracer_tokens (tracer_id int, publisher_commit datetime)

INSERT #tracer_tokens (tracer_id, publisher_commit)
EXEC AdventureWorks2014.sys.sp_helptracertokens @Publication = @lsPublication;
SET @liTokenID = 
  (
  SELECT TOP 1 tracer_id 
  FROM #tracer_tokens
  ORDER BY publisher_commit DESC
  )
DROP TABLE #tracer_tokens

CREATE TABLE #Latency 
  (
  DISTRIBUTOR_LATENCY INT, 
  SUBSCRIBER VARCHAR(255),
  SUBSCRIBER_DB VARCHAR(255),
  SUBSCRIBER_LATENCY INT,
  OVERALL_LATENCY INT
  )

INSERT INTO #Latency (distributor_latency, subscriber, subscriber_db, subscriber_latency, overall_latency)
EXEC AdventureWorks2014.sys.sp_helptracertokenhistory 
  @Publication = @lsPublication, 
  @tracer_id = @liTokenID;

IF (SELECT MAX(ISNULL(OVERALL_LATENCY, 100)) FROM #Latency) >= 59 
  BEGIN
         SET @lsMessage = 
         '<p style="font-family:Arial; font-size:10pt">There is an error in database replication.  ' + 
         'Latency has exceeded 1 minute. This can be caused by a patch, configuration or database error.  ' +
         'Please Login to publisher server, then use the following command to open <b>SQL Replication Monitor</b> to determine the error.' + '<br><br>' + 
         '<table border=1 cellspacing=0 cellpadding=0 bgcolor=#F2F2F2><tr><td><font face="Courier New" size=2>"C:\"Program Files (x86)"\"Microsoft SQL Server"\120\Tools\Binn\SqlMonitor.exe" </font></td></tr></table>' + '<br>' + 
         '<b>NOTE:</b> This alert will be generated every 15 minutes until replication is caught up for the AdventureWorks2014 databases on <b>all</b> subscribers.  ' +
         '</p>'
      EXEC msdb.dbo.sp_send_dbmail  
         @recipients='[email protected]', 
         @body=@lsMessage, 
         @subject='Replication Alert: Higher Latency', 
         @file_attachments='', 
         @copy_recipients='', 
         @blind_copy_recipients='', 
         @importance='high', 
         @body_format='HTML', 
         @profile_name=@ProfileName
  END
DROP TABLE #Latency;
SET NOCOUNT OFF
End

Create Replication Latency Check SQL Agent Job

Once the procedure is created, create a SQL Server Agent Job to execute every 15 minutes. To do that, open SQL Server Management Studio on the publisher database server. Expand the SQL instance, Expand SQL Server Agent and right click on Jobs and select "New Job." See the following image:

object explorer new job

The dialog box "New Job" opens. Provide the desired Job name and select "Steps" option in "Select a page" menu and click on "New" to add a job step. See the following image:

sql agent job

A dialog box "New Job Step" opens. In the Step Name text box, provide desired step name. In type drop-down box, select "Transact-SQL Script (T-SQL)." Copy and paste the following T-SQL code in the "Command" drop-down box.

Use AdventureWorks2014
GO
Exec [sp_ReplicationLatencyMonitor]

Click OK to close the dialog box. See the following image:

sql agent job

On the "New job" screen, select "Schedules" option and click the "New" button to add a job execution schedule. See the following image:

sql agent job

A dialog box "New Job Schedule" opens to configure the Job schedule. In Name text box, provide an appropriate name, in frequency, select "Daily" in the occurs drop down box, and in daily frequency, select 15 minutes and click ok. See the following image:

sql agent job

Once the schedule and job steps configuration complete, click OK to close the job.

Sample Replication Latency Email

When the code finds replication latency issues it sends the following email.

replication latency issue email
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-11-20

Comments For This Article




Tuesday, February 25, 2020 - 2:49:42 PM - Rod Back To Top (84794)

Excellent! One of the best articles.














get free sql tips
agree to terms