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