Monitor Transactional Replication Latency

CREATE PROCEDURE [dbo].[proc_replication_latency_monitor]
AS

-- Procedure created by Henning Frettem (Lumbago), www.sqlteam.com, 12th of June 2008

BEGIN

SET NOCOUNT ON

DECLARE
@PublishedDBName varchar(200),
@SQL nvarchar(2000),
@PublicationPK int,
@PublicationName varchar(200),
@PublicationDatabase varchar(200),
@TracerTokenID int,
@Parmameters nvarchar(500)

DECLARE @Publications table (
PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
PublicationDatabase varchar(200),
PublicationName varchar(200),
TracerTokenID int,
TracerTokenPostTime datetime
)

DECLARE @Latency table (
PublicationPK int,
DistributorLatency bigint,
Subscriber sysname,
SubscriberDB sysname,
SubscriberLatency bigint,
OverallLatency bigint
)

--> Cursor for fetching all publications in all databases
DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE is_published > 0

OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications'
INSERT INTO @Publications (PublicationDatabase, PublicationName)
EXEC sp_executesql @SQL

FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases

--> Cursor for posting tracer tokens
DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT';
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT'
EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT

UPDATE @Publications SET
TracerTokenID = @TracerTokenID,
TracerTokenPostTime = GETDATE()
WHERE PublicationPK = @PublicationPK

FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
END
CLOSE curPublications
DEALLOCATE curPublications

--> Wait two minutes for all tokens to be commited at all subscribers
WAITFOR DELAY '000:02:00.000'

--> Then check the results for each posted token
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications

OPEN curTokens
FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
BEGIN
--> Insert token history for each token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
INSERT INTO @Latency
(DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
EXEC sp_executesql @SQL

--> Make sure that the PublicationPK is added to the token history
UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL

--> Clean up the tracer token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
EXEC sp_executesql @SQL

FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
END
CLOSE curTokens
DEALLOCATE curTokens

SELECT
b.PublicationDatabase,
b.PublicationName,
a.Subscriber,
a.SubscriberDB,
a.OverallLatency,
b.TracerTokenPostTime
FROM @Latency a
INNER JOIN @Publications b
ON a.PublicationPK = b.PublicationPK

END

--
Lumbago