Export (0) Print
Expand All
Expand Minimize

sp_helptracertokens (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns one row for each tracer token that has been inserted into a publication to determine latency. This stored procedure is executed at the Publisher on the publication database or at the Distributor on the distribution database.

Topic link icon Transact-SQL Syntax Conventions

sp_helptracertokens [ @publication = ] 'publication' 
    [ , [ @publisher = ] 'publisher' ] 
    [ , [ @publisher_db = ] 'publisher_db' ]

[ @publication= ] 'publication'

Is the name of the publication in which tracer tokens were inserted. publication is sysname, with no default.

[ @publisher= ] 'publisher'

The name of the Publisher. publisher is sysname, with a default of NULL.

Note Note

This parameter should only be specified for non-Microsoft SQL Server Publishers.

[ @publisher_db= ] 'publisher_db'

The name of the publication database. publisher_db is sysname, with a default value of NULL. This parameter is ignored if the stored procedure is executed at the Publisher.

Column name

Data type

Description

tracer_id

int

Identifies a tracer token record.

publisher_commit

datetime

The date and time that the token record was committed at the Publisher in the publication database.

0 (success) or 1 (failure)

sp_helptracertokens is used in transactional replication.

sp_helptracertokens is used to obtain tracer token IDs when executing sp_helptracertokenhistory (Transact-SQL).

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

Only members of the sysadmin fixed server role, the db_owner fixed database role in the publication database, or db_owner fixed database or replmonitor roles in the distribution database can execute sp_helptracertokenhistory.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft