is there a way to monitor transactional replication latency without using tokens?

Question

When I have latency on my replicated publications, I resort to adding a token and watch it. Is there any way of troubleshooting replication latency without the use of tokens? This is SQL Server 2005 - Transactional Replication.

Answer 1

I found out this link about checking replication latency using T-SQL and created the procedure in my environment and it worked very nicely.

here is the code of the procedure, and in the comments, how I have utilized it.

USE [MY_PUBLICATION_DATABASE]
-- this procedure is to be created inside the publication database
GO
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
    ----------------------------------------------------------------------------

    Marcelo Miorelli
    01-Oct-2014 wednesday
    I found this wonderful procedure at this site:
    http://sqlfool.com/2008/11/checking-replication-latency-with-t-sql/

    I have tested it
    Server: SQLWEBLON1.DEV.BODEN.LOCAL
    DB: AUAccount

    I had to create the procedure in the Publisher database, and run it from there.

        Exec dbo.dba_replicationLatencyGet_sp
          @publicationToTest    = N'AUAccount'
        , @replicationDelay     = N'00:00:05'
        , @iterations           = 1
        , @iterationDelay       = N'00:00:05'
        , @deleteTokens         = 1
        , @deleteTempTable      = 1;

    "La observación y la percepción son dos cosas separadas; 
     el ojo que observa es más fuerte, el ojo que percibe es más débil. "
     "el libro de los 5 anillos" 
     Mushashi

    ----------------------------------------------------------------------------
    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