Today, I’ve written the following T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions. This script helps you to answer common questions such as:
- Is my transactional replication healthy?
- How far behind is my transactional subscription?
- How long will it take a transaction committed now to reach a Subscriber in transactional replication?
Prerequisites
To execute this script, you must have SELECT permission on the following tables inside distribution and master databases:
- MSdistribution_status
- MSdistribution_agents
- MSArticles
- MSreplication_monitordata
- MSdistribution_history
- servers
Code
Below is the code of this script:
USE [distribution] IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL DROP TABLE #ReplStats CREATE TABLE [dbo].[#ReplStats]( [DistributionAgentName] [nvarchar](100) NOT NULL, [DistributionAgentStartTime] [datetime] NOT NULL, [DistributionAgentRunningDurationInSeconds] [int] NOT NULL, [IsAgentRunning] [bit] NULL, [ReplicationStatus] [varchar](14) NULL, [LastSynchronized] [datetime] NOT NULL, [Comments] [nvarchar](max) NOT NULL, [Publisher] [sysname] NOT NULL, [PublicationName] [sysname] NOT NULL, [PublisherDB] [sysname] NOT NULL, [Subscriber] [nvarchar](128) NULL, [SubscriberDB] [sysname] NULL, [SubscriptionType] [varchar](64) NULL, [DistributionDB] [sysname] NULL, [Article] [sysname] NOT NULL, [UndelivCmdsInDistDB] [int] NULL, [DelivCmdsInDistDB] [int] NULL, [CurrentSessionDeliveryRate] [float] NOT NULL, [CurrentSessionDeliveryLatency] [int] NOT NULL, [TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL, [TotalCommandsDeliveredInCurrentSession] [int] NOT NULL, [AverageCommandsDeliveredInCurrentSession] [int] NOT NULL, [DeliveryRate] [float] NOT NULL, [DeliveryLatency] [int] NOT NULL, [TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL, [SequenceNumber] [varbinary](16) NULL, [LastDistributerSync] [datetime] NULL, [Retention] [int] NULL, [WorstLatency] [int] NULL, [BestLatency] [int] NULL, [AverageLatency] [int] NULL, [CurrentLatency] [int] NULL ) ON [PRIMARY] INSERT INTO #ReplStats SELECT da.[name] AS [DistributionAgentName] ,dh.[start_time] AS [DistributionAgentStartTime] ,dh.[duration] AS [DistributionAgentRunningDurationInSeconds] ,md.[isagentrunningnow] AS [IsAgentRunning] ,CASE md.[status] WHEN 1 THEN '1 - Started' WHEN 2 THEN '2 - Succeeded' WHEN 3 THEN '3 - InProgress' WHEN 4 THEN '4 - Idle' WHEN 5 THEN '5 - Retrying' WHEN 6 THEN '6 - Failed' END AS [ReplicationStatus] ,dh.[time] AS [LastSynchronized] ,dh.[comments] AS [Comments] ,md.[publisher] AS [Publisher] ,da.[publication] AS [PublicationName] ,da.[publisher_db] AS [PublisherDB] ,CASE WHEN da.[anonymous_subid] IS NOT NULL THEN UPPER(da.[subscriber_name]) ELSE UPPER (s.[name]) END AS [Subscriber] ,da.[subscriber_db] AS [SubscriberDB] ,CASE da.[subscription_type] WHEN '0' THEN 'Push' WHEN '1' THEN 'Pull' WHEN '2' THEN 'Anonymous' ELSE CAST(da.[subscription_type] AS [varchar](64)) END AS [SubscriptionType] ,md.[distdb] AS [DistributionDB] ,ma.[article] AS [Article] ,ds.[UndelivCmdsInDistDB] ,ds.[DelivCmdsInDistDB] ,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate] ,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency] ,dh.[delivered_transactions] AS [TotalTransactionsDeliveredInCurrentSession] ,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession] ,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession] ,dh.[delivery_rate] AS [DeliveryRate] ,dh.[delivery_latency] AS [DeliveryLatency] ,dh.[total_delivered_commands] AS [TotalCommandsDeliveredSinceSubscriptionSetup] ,dh.[xact_seqno] AS [SequenceNumber] ,md.[last_distsync] AS [LastDistributerSync] ,md.[retention] AS [Retention] ,md.[worst_latency] AS [WorstLatency] ,md.[best_latency] AS [BestLatency] ,md.[avg_latency] AS [AverageLatency] ,md.[cur_latency] AS [CurrentLatency] FROM [distribution]..[MSdistribution_status] ds INNER JOIN [distribution]..[MSdistribution_agents] da ON da.[id] = ds.[agent_id] INNER JOIN [distribution]..[MSArticles] ma ON ma.publisher_id = da.publisher_id AND ma.[article_id] = ds.[article_id] INNER JOIN [distribution]..[MSreplication_monitordata] md ON [md].[job_id] = da.[job_id] INNER JOIN [distribution]..[MSdistribution_history] dh ON [dh].[agent_id] = md.[agent_id] AND md.[agent_type] = 3 INNER JOIN [master].[sys].[servers] s ON s.[server_id] = da.[subscriber_id] --Created WHEN your publication has the immediate_sync property set to true. This property dictates --whether snapshot is available all the time for new subscriptions to be initialized. --This affects the cleanup behavior of transactional replication. If this property is set to true, --the transactions will be retained for max retention period instead of it getting cleaned up --as soon as all the subscriptions got the change. WHERE da.[subscriber_db] <> 'virtual' AND da.[anonymous_subid] IS NULL AND dh.[start_time] = (SELECT TOP 1 start_time FROM [distribution]..[MSdistribution_history] a JOIN [distribution]..[MSdistribution_agents] b ON a.[agent_id] = b.[id] AND b.[subscriber_db] <> 'virtual' WHERE [runstatus] <> 1 ORDER BY [start_time] DESC) AND dh.[runstatus] <> 1 SELECT 'Transactional Replication Summary' AS [Comments]; SELECT [DistributionAgentName] ,[DistributionAgentStartTime] ,[DistributionAgentRunningDurationInSeconds] ,[IsAgentRunning] ,[ReplicationStatus] ,[LastSynchronized] ,[Comments] ,[Publisher] ,[PublicationName] ,[PublisherDB] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] ,[CurrentSessionDeliveryRate] ,[CurrentSessionDeliveryLatency] ,[TotalTransactionsDeliveredInCurrentSession] ,[TotalCommandsDeliveredInCurrentSession] ,[AverageCommandsDeliveredInCurrentSession] ,[DeliveryRate] ,[DeliveryLatency] ,[TotalCommandsDeliveredSinceSubscriptionSetup] ,[SequenceNumber] ,[LastDistributerSync] ,[Retention] ,[WorstLatency] ,[BestLatency] ,[AverageLatency] ,[CurrentLatency] FROM #ReplStats GROUP BY [DistributionAgentName] ,[DistributionAgentStartTime] ,[DistributionAgentRunningDurationInSeconds] ,[IsAgentRunning] ,[ReplicationStatus] ,[LastSynchronized] ,[Comments] ,[Publisher] ,[PublicationName] ,[PublisherDB] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,[CurrentSessionDeliveryRate] ,[CurrentSessionDeliveryLatency] ,[TotalTransactionsDeliveredInCurrentSession] ,[TotalCommandsDeliveredInCurrentSession] ,[AverageCommandsDeliveredInCurrentSession] ,[DeliveryRate] ,[DeliveryLatency] ,[TotalCommandsDeliveredSinceSubscriptionSetup] ,[SequenceNumber] ,[LastDistributerSync] ,[Retention] ,[WorstLatency] ,[BestLatency] ,[AverageLatency] ,[CurrentLatency] SELECT 'Transactional Replication Summary Details' AS [Comments]; SELECT [Publisher] ,[PublicationName] ,[PublisherDB] ,[Article] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] FROM #ReplStats GROUP BY [Publisher] ,[PublicationName] ,[PublisherDB] ,[Article] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB]
All comments and additions are welcome 
.
