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

Rate

5 (2)

Share

Share

Rate

5 (2)