Custom Replication Status Record Latency Monitor

Why did I write it?

I wrote this years ago as a "means to an end" when trying to determine why our daily reporting process appeared stuck.  After checking the built-in replication monitor of your transact replication solution and only seeing it was "30 seconds of latency" or 800,000 undistributed commands behind, I decided I wanted to be able to more quickly find out "which tables" were actually behind, and automate an email that would get sent stating such.  This script provides this information, as needed, and "when" needed.

What does it do?

This script compares the row counts between a specificed set of tables between the Publisher to those of the Subscriber. The built-in replication monitor, shows "number of commands in the distribution database waiting to be applied at the subscriber" This script shows which tables are behind and how many rows it needs to catch up on.

How can you use it?

For cases where real-time data is important, I've employed it's use in 2 scenarios (where our SLA of 3 minutes latency is deemed acceptable).

• SQL Agent Jobs: In situations where a scheduled job needs to run but requires that data be current, you can add this procedure prior to the code that gets executed (or as a prior job step) and it will keep checking to ensure the record counts match before moving on.
• Stored-procedures: Within other procedures, where data must be current, you can add this procedure prior to the code that gets executed to ensure the record counts match before moving on

Example usage:

EXEC MyDatabase.dbo.dba_CheckReplicatedTableCounts 

    @Publisher = 'Publisher', 

    @Subscriber = 'Subscriber', 

    @DB = 'MyDatabase', 

    @Tables = 'Table1,Table2,Table3,Table4,Table5,Etc', 

    @Threshold = 15, 

    @RunningFrom = 'DAILY - RUN INVOICE REPORTS', 

    @WaitTime = '00:00:03', 

    @EmailAfterLoops = 20, 

    @SuppressMsg = 0

An example of the email notification generated:

What it does NOT do:

• It will NOT ensure that your data is accurately updated
• It will NOT check for any in-flight data manipulations
• It will NOT give you a raise

Known Dependencies

1. dbo.fx_FormatArrayText() - this is a sinmple scalar function that will take a comma delimited string and format it for use in dynamic SQL.  It has been included in the SQL Scripts for this article
2. If your replication topology involves seperate Publsher/Subscriber/Distrbutor, you will need to create a the appropriate linked servers to those instances

Known Issues

None at this time

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fx_FormatArrayText] (
@String varchar(1500), 
@Delimiter char(1), 
@NumberQuotes int = 1)
RETURNS varchar(1500) AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose: Convert delimited text within a string into parenthesized values (quotes optional)
Department:
Created For: 
----------------------------------------------------------------------------------------------------------------
NOTES: @Delimiter - Tells function the delimiter to parse the text with
@NumberQuotes - How many quotes you wish to have in the OUTPUT string
----------------------------------------------------------------------------------------------------------------
Created On: 10/20/2005
Created By: MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Modified On: 
Modified By: 
Changes:
1.
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_FormatArrayText('Table1|Table2|Table3|Table4','|', 1)
*/BEGIN

DECLARE @Quote varchar(10)
SET @Quote = ''

/* ######################################### START MAIN FUNCTION HERE ########################################## */
IF @NumberQuotes >= 1
BEGIN
SET @Quote = SPACE(@NumberQuotes)
SET @Quote = REPLACE(@Quote, ' ', '''')
END

IF @Delimiter = ' '
BEGIN
/* Eliminate double spaces in text string */WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', ' ')
END
END

ELSE
BEGIN
/* Eliminate all spaces in text string */WHILE CHARINDEX(' ', RTRIM(@String)) <> 0
BEGIN
SET @String = REPLACE(@String, ' ', '')
END
END

/* Convert supplied delimiter with open quotes, comma, and close quotes */SET @String = REPLACE(@String, @Delimiter, @Quote + ',' + @Quote)

/* Add opening and closing quotes and parentheses */SET @String = '(' + @Quote + @String + @Quote + ')'

/* ########################################## END MAIN END HERE ########################################### */RETURN @String
END

GO


CREATE PROCEDURE [dbo].[dba_CheckReplicatedTableCounts] (
 @Publisher varchar(50),
@Subscriber varchar(50),
@DB varchar(50), 
@Tables varchar(750),
@Threshold varchar(7),
@RunningFrom varchar(250) = NULL,
@WaitTime varchar(12) = '00:01:00',
@EmailAfterLoops int = 20,
@SuppressMsg tinyint = 0
) AS
/*
--------------------------------------------------------------------------------------------------------------------------------------------
Purpose: Monitors specific replicated tables for any publisher/subscriber for latency in record counts and emails a notification to a specific 
list of recipients
Department: 
Created For: 
--------------------------------------------------------------------------------------------------------------------------------------------
NOTES:<< Procedure was designed to run at the subscriber, but can be deployed to any server that has appropriate linked servers defined >>
@Publisher is the linked server name to your Publisher
@Subscriber is the linked server name to the Subscriber
@Tables is the tables you want to compare records counts between the publisher/subscriber; If you want ALL REPLICATED TABLES, set this 
parameter to NULL
@Threshold is the record count it can be behind before triggering the email
@RunningFrom is where this alert gets triggered from. For instance, if this was automated, you would list the SQL Agent Job Name here, if 
it was coming from another procedure, you'd use the procedure name, etc.
@WaitTime is the value to wait before looping to check the record counts again, no value default to 1 minute interval
@EmailAfterLoops is what determines when to send an email. You'll receive an email after the @WaitTime * @EmailAfterLoops
So if @WaitFor = '00:00:03' and @EmailAfterLoops = 20, you'll receive an email in 1 minute (3 x 20 = 60) - and keep receiving them
--------------------------------------------------------------------------------------------------------------------------------------------
DEPENDENCIES:
dbo.fx_FormatArrayText() >> Scalar function that parses the table list to be used in dynamic SQL. For instance:
SELECT dbo.fx_FormatArrayText('Table1|Table2|Table3|Table4','|', 1) returns:
('Table1','Table2','Table3','Table4')
--------------------------------------------------------------------------------------------------------------------------------------------
Created On: 08/01/2014
Created By: Serge Mirault
--------------------------------------------------------------------------------------------------------------------------------------------
Modified On: 
Modified By: 
Changes:
1.
--------------------------------------------------------------------------------------------------------------------------------------------
Example Executions:
EXEC MyDatabase..dba_CheckReplicatedTableCounts 
@Publisher = 'Publisher', 
@Subscriber = 'Subscriber', 
@DB = 'MyDatabase', 
@Tables = 'Table1,Table2,Tabl3,Etc', 
@Threshold = 15, 
@RunningFrom = 'SSMS', 
@WaitTime = '00:00:03', 
@EmailAfterLoops = 20, 
@SuppresMsg = 0

An automated alert will be send every 1 minutes if any of the tables list have > 15 record latency
*/SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @SendExternalEmail tinyint, @To varchar(250), @Bcc varchar(250)
SELECT @SendExternalEmail  = 'dba@yourcompany.com'

DECLARE @SQL nvarchar(4000), @TempTable nvarchar(25), @Count int, @Filter1 nvarchar(850) = '', @Filter2 nvarchar(850) = '', @LoopCnt int
DECLARE @Subject varchar(250), @Body varchar(1500), @TableName varchar(50), @RowCount varchar(20), @idx int
DECLARE @Results TABLE (col1 varchar(50), col2 int, col3 varchar(50), col4 int, col5 int)

SET @LoopCnt  = 1

/* Do we have tables being passed in? */IF (@Tables IS NOT NULL )
BEGIN
SET @Filter1 = 'WHERE st.name IN ' + dbo.fx_FormatArrayText(@Tables, ',', 1) + ''
SET @Filter2 = 'AND st.name IN ' + dbo.fx_FormatArrayText(@Tables, ',', 1) + ''
END

/* Create global temp tables based upon procedure call and for which database you have replicated; in the case below I have
4 database published in my replication architecture */BEGIN
IF @DB = 'MyDatabase'
BEGIN
SET @TempTable = '##' + @DB + CAST(ROUND(((99999 - 2-50) * RAND() + 2), 0) AS VARCHAR(5))
SET @SQL = 'CREATE TABLE ' + @TempTable + ' (col1 varchar(50), col2 int, col3 varchar(50), col4 int, col5 int)'
EXECUTE Master..sp_ExecuteSQL @SQL
END

IF @DB = 'MyOtherDatabase'
BEGIN
SET @TempTable = '##' + @DB + CAST(ROUND(((99999 - 2-50) * RAND() + 2), 0) AS VARCHAR(5))
SET @SQL = 'CREATE TABLE ' + @TempTable + ' (col1 varchar(50), col2 int, col3 varchar(50), col4 int, col5 int)'
EXECUTE Master..sp_ExecuteSQL @SQL
END

IF @DB = 'MyOtherDatabaseAgain'
BEGIN
SET @TempTable = '##' + @DB + CAST(ROUND(((99999 - 2-50) * RAND() + 2), 0) AS VARCHAR(5))
SET @SQL = 'CREATE TABLE ' + @TempTable + ' (col1 varchar(50), col2 int, col3 varchar(50), col4 int, col5 int)'
EXECUTE Master..sp_ExecuteSQL @SQL
END

IF @DB = 'MyOtherOtherDatabase'
BEGIN
SET @TempTable = '##' + @DB + CAST(ROUND(((99999 - 2-50) * RAND() + 2), 0) AS VARCHAR(5))
SET @SQL = 'CREATE TABLE ' + @TempTable + ' (col1 varchar(50), col2 int, col3 varchar(50), col4 int, col5 int)'
EXECUTE Master..sp_ExecuteSQL @SQL
END
END

CHECKDATA:
BEGIN
SET @SQL = N'
WITH Subscriber AS (
SELECT
sch.name AS SchemaName,
st.Name AS TableName,
SUM(CASE WHEN (p.index_id < 2)
  AND (a.type = 1) THEN p.rows
 ELSE 0
END) AS Rows
  FROM ' + QUOTENAME(@Subscriber) + '.' + @DB + '.sys.partitions p WITH(READUNCOMMITTED)
  INNER JOIN ' + QUOTENAME(@Subscriber) + '.' + @DB + '.sys.allocation_units a WITH(READUNCOMMITTED)
ON p.partition_id = a.container_id
  INNER JOIN ' + QUOTENAME(@Subscriber) + '.' + @DB + '.sys.tables st WITH(READUNCOMMITTED)
ON st.object_id = p.Object_ID
  INNER JOIN ' + QUOTENAME(@Subscriber) + '.' + @DB + '.sys.schemas sch WITH(READUNCOMMITTED)
ON sch.schema_id = st.schema_id
' + @Filter1 + '
  GROUP BY
st.name,
sch.name),
Publisher AS (
SELECT
sch.name AS SchemaName,
st.Name AS TableName,
SUM(CASE WHEN (p.index_id < 2)
  AND (a.type = 1) THEN p.rows
 ELSE 0
END) AS Rows
  FROM ' + QUOTENAME(@Publisher) + '.' + @DB + '.sys.partitions p WITH(READUNCOMMITTED)
  INNER JOIN ' + QUOTENAME(@Publisher) + '.' + @DB + '.sys.allocation_units a WITH(READUNCOMMITTED)
ON p.partition_id = a.container_id
  INNER JOIN ' + QUOTENAME(@Publisher) + '.' + @DB + '.sys.tables st WITH(READUNCOMMITTED)
ON st.object_id = p.Object_ID
  INNER JOIN ' + QUOTENAME(@Publisher) + '.' + @DB + '.sys.schemas sch WITH(READUNCOMMITTED)
ON sch.schema_id = st.schema_id
  WHERE p.rows > 0 AND st.is_published = 1
' + @Filter2 + '
  GROUP BY
st.name,
sch.name
)

INSERT INTO ' + @TempTable + '
SELECT 
s.TableName,
s.Rows,
p.TableName AS BTableName,
p.Rows AS BRows,
s.ROWS - p.Rows AS Delta
FROM Subscriber AS s
INNER JOIN Publisher AS p
ON s.TableName = p.TableName
AND s.SchemaName = p.SchemaName
AND s.ROWS <> p.rows
AND (s.ROWS - p.Rows) < -' + @Threshold + '
'
EXEC sp_executeSQL @SQL 
END

/* Capture results from the global temp table */BEGIN
SET @SQL = 'SELECT * FROM ' + @TempTable
INSERT INTO @Results
EXECUTE master..sp_ExecuteSQL @SQL
SET @Count = @@ROWCOUNT
END

/* If > 0 we're behind, starting checking, send an email to let everyone know */WHILE  (@Count) <> 0
BEGIN 
SET @Subject = CAST(@@SERVERNAME as varchar(25)) + ' :: ' + @DB + ' Replication is behind for the following tables'
DECLARE @Table TABLE (idx int IDENTITY(1,1), TableName varchar(50), [RowCount] varchar(20))
INSERT INTO @Table
SELECT col3, col5 FROM @Results

SET @Body = '<p style="font-size:12px;font-family:Verdana"><font color="red">The following replcated tables are currently behind.<br>'
+ 'The ' + ISNULL(@RunningFrom, 'reporting') + ' job will be temporarily "paused" until the threshold (' 
+ @Threshold + ' replicated records) has been met.</font><br>' 
+ '=========================================================================================<br>' 
WHILE (SELECT TOP 1 idx FROM @Table) > 0
BEGIN
SELECT @idx = idx, @TableName = TableName, @RowCount = [RowCount] FROM @Table
SELECT @Body = @Body  + @TableName + '  ' + @RowCount + '<br>'
DELETE FROM @Table WHERE idx = @idx
END

/* Used this notification to send an alert to whoever needs to know - an example is below */SET @Body = @Body + '<br><br>[Some important about the process should go here], please notify someonewhocares@yourcompany.com and/or otherbigwigs@yourcompany.com IMMEDIATELY to let them know things may be falling behind</p>'

/* Send email every "X" interations through the loop (to cut down on email notifications) */IF @LoopCnt % @EmailAfterLoops = 0
BEGIN
PRINT @LoopCnt
EXEC msdb..sp_send_dbmail @recipients = @Bcc, @Subject = @Subject, @body = @body, @body_format = 'HTML'
END

WAITFOR DELAY @WaitTime

/* Clear the temp tables so we don't get stuck in a loop */BEGIN
SET @Count = 0
SET @SQL = 'TRUNCATE TABLE ' + @TempTable
EXECUTE master..sp_ExecuteSQL @SQL
DELETE FROM @Results
END

SET @LoopCnt = @LoopCnt + 1
GOTO CHECKDATA
END 

/* Clean up the global temp tables - no longer needed */BEGIN
SET @SQL = 'DROP TABLE ' + @TempTable
EXECUTE master..sp_ExecuteSQL @SQL
END

IF (@Tables IS NOT NULL )
BEGIN
IF @SuppressMsg = 0
BEGIN
SELECT 'Replication is NOT currently behind for the following tables: ' + @Tables + ' in the ' + @DB + ' database!'
END
END
ELSE
BEGIN
IF @SuppressMsg = 0
BEGIN
SELECT 'Replication is NOT currently behind for any replication in the ' + @DB + ' database!'
END
END