http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/07/25/t-sql-script-to-monitor-transactional-replication-status/

Printed 2014/10/21 04:00AM

Transact-SQL Script to Monitor Replication Status

2012/07/25

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:

Prerequisites

To execute this script, you must have SELECT permission on the following tables inside distribution and master databases:

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 :-D .



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.