SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Custom Replication Status Record Latency Monitor

By MyDoggieJessie,

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

Total article views: 160 | Views in the last 30 days: 3
Related Articles

SQL Server Replication with SS2K Publisher and SS2K5 Subscriber

How can I move the replication stored procedures for a transactional replication to the SS2K5 subscr...


Transaction Replication: Multiple Publishers, Single Subscriber

How to configure multiple publishers and single subscriber replication?


Monitor Replication Using Scripts

Monitor Replication Using Scripts


Stairway to SQL Server Replication: Level 4 - Transactional Replication – The Subscriber

The Subscriber is the server where all the changes that are published by replication get delivered t...


Repairing a replication subscriber

It’s a question which has come up a couple of times. If a subscriber of a transactional replication ...