Checking Replication Latency with T-SQL

This post may only appeal to small subset of DBA’s. :)

This particular script was born of a need to view replication latency in production. I had to investigate whether the replication latency issues we were experiencing in production were occurring on the publisher (us) or the subscriber (them), and address accordingly. “Why just not use Replication Monitor?”, you might ask. Good question! I would definitely use Replication Monitor if it were available to me; however, I do not have the necessary permissions to the Distributor. :(

Fortunately, SQL 2005 provides us with some tools to help: sp_postTracerToken and sp_helpTracerTokenHistory. This allows us to access the same Tracer Tokens feature that is in Replication Monitor.


Replication Monitor - Tracer Tokens

 

So that’s the backstory, and here’s the script I came up with. For those interested, it’d be pretty easy to modify this script to use a permanent table and regularly log replication latency.

Create Procedure dbo.dba_replicationLatencyGet_sp
 
        /* Declare Parameters */
          @publicationToTest sysname        = N'yourPublicationName'
        , @replicationDelay  varchar(10)    = N'00:00:30'
        , @iterations        int            = 5
        , @iterationDelay    varchar(10)    = N'00:00:30'
        , @deleteTokens      bit            = 1
        , @deleteTempTable   bit            = 1
As
/*********************************************************************************
    Name:       dba_replicationLatencyGet_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves the amount of replication latency in seconds
 
    Notes:      Default settings will run 1 test every minute for 5 minutes.
 
                @publicationToTest = change the default to your publication
 
                @replicationDelay = how long to wait for the token to replicate;
                    probably should not set to anything less than 10 (in seconds)
 
                @iterations = how many tokens you want to test
 
                @iterationDelay = how long to wait between sending test tokens
                    (in seconds)
 
                @deleteTokens = whether you want to retain tokens when done
 
                @deleteTempTable = whether or not to retain the temporary table
                    when done.  Data stored to ##tokenResults; set @deleteTempTable 
                    flag to 0 if you do not want to delete when done.
 
    Called by:  DBA
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2008-11-20   MFU        Initial Release
*********************************************************************************
    Exec dbo.dba_replicationLatencyGet_sp
          @publicationToTest    = N'yourPublicationName'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @deleteTokens         = 1
        , @deleteTempTable      = 1;
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare Variables */
    Declare @currentIteration   int
          , @tokenID            bigint
          , @currentDateTime    smalldatetime;
 
    If Object_ID('tempdb.dbo.##tokenResults') Is Null
    Begin
        Create Table ##tokenResults
                        ( iteration           int             Null
                        , tracer_id           int             Null
                        , distributor_latency int             Null
                        , subscriber          varchar(1000)   Null
                        , subscriber_db       varchar(1000)   Null
                        , subscriber_latency  int             Null
                        , overall_latency     int             Null );
    End;
 
    /* Initialize our variables */
    Select @currentIteration = 0
         , @currentDateTime  = GetDate();
 
    While @currentIteration < @iterations
    Begin
 
        /* Insert a new tracer token in the publication database */
        Execute sys.sp_postTracerToken 
          @publication = @publicationToTest,
          @tracer_token_id = @tokenID OutPut;
 
        /* Give a few seconds to allow the record to reach the subscriber */
        WaitFor Delay @replicationDelay;
 
        /* Store our results in a temp table for retrieval later */
        Insert Into ##tokenResults
        (
            distributor_latency
          , subscriber
          , subscriber_db
          , subscriber_latency
          , overall_latency
        )
        Execute sys.sp_helpTracerTokenHistory @publicationToTest, @tokenID;
 
        /* Assign the iteration and token id to the results for easier investigation */
        Update ##tokenResults
        Set iteration = @currentIteration + 1
          , tracer_id = @tokenID
        Where iteration Is Null;
 
        /* Wait for the specified time period before creating another token */
        WaitFor Delay @iterationDelay;
 
        /* Avoid endless looping... :) */
        Set @currentIteration = @currentIteration + 1;
 
    End;
 
    Select * From ##tokenResults;
 
    If @deleteTempTable = 1
    Begin
        Drop Table ##tokenResults;
    End;
 
    If @deleteTokens = 1
    Begin
       Execute sp_deleteTracerTokenHistory @publication = @publicationToTest, @cutoff_date = @currentDateTime;
    End;
 
    Set NoCount Off;
    Return 0;
End
Go
0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

7 Responses to Checking Replication Latency with T-SQL

  1. ILKirk says:

    You know, I was sitting on the plane coming home from PASS and I thought – I really need to write a script with those two procedures to log and monitor replication latency… and then this.

    Thanks for sharing – I’ll likely be testing this Monday and will let you know if I spot anything wonky!

  2. You’re welcome! Yes, please do let me know if anything pops up. Right now this proc just tells me how replication is currently performing… I’m toying with the idea of modifying this proc to log regularly for long-term analysis.

    So… any favorite tips / moments from PASS? :)

  3. ILKirk says:

    A) The Transactional Replication session – I was so happy to have chosen that one, considering how much I’ve had to deal with replication in my current post lately.

    B) Realizing I’ve been missing out on so much by sort of skipping the online community – the people in Twitter, the various communities, blogs, etc. It’s driving me to be more active…

    C) Buck Woody’s amazingly thorough and amusing presentation on Powershell – I was already excited about PS and now I am moreso.

  4. Pingback: Replication Monitor : SQL Fool

  5. jessica says:

    this is a great post!, how would this sp change if i have over a 100 publications?

  6. Adam says:

    Thanks for allowing me to steal this code and saving me some valuable time.

  7. Naveed says:

    Excellent Script :) I have a plan to to put the sproc in a job and run it every 5 – 10 minutes for monitoring purposes. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>