This topic has not yet been rated - Rate this topic

sp_posttracertoken (Transact-SQL)

SQL Server 2012

SQL Server 2005

This procedure posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics. Information is recorded when the tracer token is written to the transaction log, when it is picked up by the Log Reader Agent, and when it is applied by the Distribution Agent. This stored procedure is executed at the Publisher on the publication database. For more information, see Measure Latency and Validate Connections for Transactional Replication.

Topic link icon Transact-SQL Syntax Conventions

Syntax


          

sp_posttracertoken [ @publication = ] 'publication' 
    [ , [ @tracer_token_id = ] tracer_token_id OUTPUT
    [ , [ @publisher = ] 'publisher'

Arguments

[ @publication= ] 'publication': Is the name of the publication for which latency is being measured. publication is sysname, with no default.
[ @tracer_token_id= ] tracer_token_idOUTPUT: Is the ID of the tracer token inserted. tracer_token_id is int with a default of NULL, and it is an OUTPUT parameter. This value can be used to execute sp_helptracertokenhistory (Transact-SQL) or sp_deletetracertokenhistory (Transact-SQL) without first executing sp_helptracertokens (Transact-SQL).
[ @publisher= ] 'publisher': Specifies a non-Microsoft SQL Server Publisher. publisher is sysname, with a default of NULL and should not be specified for a SQL Server Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_posttracertoken is used in transactional replication.

Example

TSQL

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

USE [AdventureWorks2012]

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

Permissions

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_posttracertoken.

Concepts

Measure Latency and Validate Connections for Transactional Replication

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD