Transactional Replication – Tracer Tokens

USE Distribution
GO

DECLARE @startTime as DATETIME = GETDATE();
DECLARE @tsql AS NVARCHAR(MAX) = '';

SELECT @tsql  = @tsql + 'EXEC [' + publisher_db + '].[sys].[sp_posttracertoken] @publication = ''' + publication + ''';'  + char(13) + char(10)
  FROM [distribution].[dbo].[MSpublications]

exec sp_executesql @tsql;

DECLARE @loopMax as INTEGER = 60;
WHILE (
		SELECT COUNT(*) 
		FROM [distribution].[dbo].[MStracer_tokens] t
		INNER JOIN [distribution].[dbo].[MStracer_history] h ON t.tracer_id = h.parent_tracer_id
		INNER JOIN [distribution].[dbo].[MSpublications] p on t.publication_id = p.publication_id
		WHERE t.publisher_commit >= @startTime
		and h.subscriber_commit is NULL ) > 0 AND @loopMax > 0
BEGIN
	WAITFOR DELAY '00:00:01';
	SET @loopMax = @loopMax -1;
END


SELECT p.[publisher_db],
       p.[publication],
	   convert(smalldatetime,t.publisher_commit) 'PubCommit',
       convert(smalldatetime,t.distributor_commit) 'DistCommit',
       convert(smalldatetime,h.subscriber_commit) 'SubCommit',
       Datediff(s,t.publisher_commit,t.distributor_commit) as 'Dist(sec)',
       Datediff(s,t.distributor_commit,h.subscriber_commit) as 'Sub(sec)'
FROM [distribution].[dbo].[MStracer_tokens] t
INNER JOIN [distribution].[dbo].[MStracer_history] h ON t.tracer_id = h.parent_tracer_id
INNER JOIN   [distribution].[dbo].[MSpublications] p on t.publication_id = p.publication_id
WHERE t.publisher_commit >= @startTime
ORDER BY t.publisher_commit DESC;