sp_helptracertokenhistory (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Returns detailed latency information for specified tracer tokens, with one row being returned for each Subscriber. This stored procedure is executed at the Publisher on the publication database or at the Distributor on the distribution database.
Transact-SQL syntax conventions
Syntax
sp_helptracertokenhistory
[ @publication = ] N'publication'
, [ @tracer_id = ] tracer_id
[ , [ @publisher = ] N'publisher' ]
[ , [ @publisher_db = ] N'publisher_db' ]
[ ; ]
Arguments
[ @publication = ] N'publication'
The name of the publication in which the tracer token was inserted. @publication is sysname, with no default.
[ @tracer_id = ] tracer_id
The ID of the tracer token in the MStracer_tokens table, for which history information is returned. @tracer_id is int, with no default.
[ @publisher = ] N'publisher'
The name of the Publisher. @publisher is sysname, with a default of NULL
.
@publisher should only be specified for non-SQL Server Publishers.
[ @publisher_db = ] N'publisher_db'
The name of the publication database. @publisher_db is sysname, with a default of NULL
. This parameter is ignored if the stored procedure is executed at the Publisher.
Result set
Column name | Data type | Description |
---|---|---|
distributor_latency |
bigint | Number of seconds between the tracer token record being committed at the Publisher and the record being committed at the Distributor. |
subscriber |
sysname | Name of the Subscriber that received the tracer token. |
subscriber_db |
sysname | Name of the subscription database into which the tracer token record was inserted. |
subscriber_latency |
bigint | Number of seconds between the tracer token record being committed at the Distributor and the record being committed at the Subscriber. |
overall_latency |
bigint | Number of seconds between the tracer token record being committed at the Publisher and token record being committed at the Subscriber. |
Return code values
0
(success) or 1
(failure).
Remarks
sp_helptracertokenhistory
is used in transactional replication.
Execute sp_helptracertokens to obtain a list of tracer tokens for the publication.
A value of NULL
in the result set means that latency statistics can't be calculated. This is because the tracer token hasn't been received at the Distributor or one of the Subscribers.
Examples
DECLARE @publication AS sysname;
DECLARE @tokenID AS int;
SET @publication = N'AdvWorksProductTran';
USE [AdventureWorks2022]
-- 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, 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
.
Related content
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें