Share via

  • Article

How to: Measure Latency and Validate Connections for Transactional Replication (Replication Transact-SQL Programming)

For transactional replication, it is important to be able to verify that servers are connected and to measure latency. Latency is the time that elapses for changes made at the Publisher to be propagated to Subscribers. For more information, see Monitoring (Replication). This information can be obtained programmatically using replication stored procedures.

Note

Tracer token information is retained for the same time period as other historical data, which is governed by the history retention period of the distribution database. To change the retention period, change the value of the history_retention property using sp_changedistributiondb (Transact-SQL).

To post a tracer token to a transactional publication

  1. (Optional) At the Publisher on the publication database, execute sp_helppublication (Transact-SQL). Verify that the publication exists and that the status is active.

  2. (Optional) At the Publisher on the publication database, execute sp_helpsubscription (Transact-SQL). Verify that the subscription exists and that the status is active.

  3. At the Publisher on the publication database, execute sp_posttracertoken (Transact-SQL), specifying @publication. Note the value of the @tracer_token_id output parameter.

To determine latency and validate connections for a transactional publication

  1. Post a tracer token to the publication using the previous procedure.

  2. At the Publisher on the publication database, execute sp_helptracertokens (Transact-SQL), specifying @publication. This returns a list of all tracer tokens posted to the publication. Note the desired tracer_id in the result set.

  3. At the Publisher on the publication database, execute sp_helptracertokenhistory (Transact-SQL), specifying @publication and the tracer token ID from step 2 for @tracer_id. This returns latency information for the selected tracer token.

To remove tracer tokens

  1. At the Publisher on the publication database, execute sp_helptracertokens (Transact-SQL), specifying @publication. This returns a list of all tracer tokens posted to the publication. Note the tracer_id for the tracer token to delete in the result set.

  2. At the Publisher on the publication database, execute sp_deletetracertokenhistory (Transact-SQL), specifying @publication and the ID of the tracer to delete from step 2 for @tracer_id.

Example

This example posts a tracer token record and uses the returned ID of the posted tracer token to view latency information.

DECLARE @publication AS sysname;
DECLARE @tokenID AS int;
SET @publication = N'AdvWorksProductTran'; 

USE [AdventureWorks2008R2]

-- Insert a new tracer token in the publication database.
EXEC sys.sp_posttracertoken 
  @publication = @publication,
  @tracer_token_id = @tokenID OUTPUT;
SELECT 'The ID of the new tracer token is ''' + 
    CONVERT(varchar,@tokenID) + '''.'
GO

-- Wait 10 seconds for the token to make it to the Subscriber.
WAITFOR DELAY '00:00:10';
GO

-- Get latency information for the last inserted token.
DECLARE @publication AS sysname;
DECLARE @tokenID AS int;
SET @publication = N'AdvWorksProductTran'; 

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime)

-- Return tracer token information to a temp table.
INSERT #tokens (tracer_id, publisher_commit)
EXEC sys.sp_helptracertokens @publication = @publication;
SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens
ORDER BY publisher_commit DESC)
DROP TABLE #tokens

-- Get history for the tracer token.
EXEC sys.sp_helptracertokenhistory 
  @publication = @publication, 
  @tracer_id = @tokenID;
GO