Monitoring Transactional Replication – The Distribution Queue

Note : The scripts for this article can be downloaded here.

Monitoring the distribution queue is an important link in the chain of transactions delivery over a replicated topology. A chain that includes the log reader agent scanning the published database’s transaction log for commands to be moved to the distribution database from which a distribution agent delivers the commands to the subscription database typically located on the subscriber server.

In an upcoming articles I will be examining other key links in that chain but for now let’s focus on the distribution database and see what information we can get there and how we can use that information for monitoring transactional replication.

The bellow SQL statement returns valuable information on the current status of the distribution database in terms of commands delivery. It allows us to see the number of commands delivered and the number of commands not yet delivered at the article level per each subscriber.

USE distribution;

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT DISTINCT

       @@SERVERNAME

      –,a.article_id

      ,a.Article

      ,p.Publication

      ,SUBSTRING(agents.[name], 16, 35) AS [Name]

      –,s.agent_id

      ,s.UndelivCmdsInDistDB

      ,s.DelivCmdsInDistDB 

–,UndelivCmdsInDistDB + DelivCmdsInDistDB  AS TaotalTrans

FROM dbo.MSdistribution_status AS s

INNER JOIN dbo.MSdistribution_agents AS agents ON agents.[id] = s.agent_id

INNER JOIN dbo.MSpublications AS p ON p.publication = agents.publication

INNER JOIN dbo.MSarticles AS a ON a.article_id = s.article_id and p.publication_id = a.publication_id

WHERE 1=1 AND s.UndelivCmdsInDistDB <> 0 AND agents.subscriber_db NOT LIKE ‘virtual’

–AND p.Publisher_db = ”

–AND a.Article LIKE  ”

–AND p.Publication IN (”)

–AND s.agent_id NOT IN ()

ORDER BY s.UndelivCmdsInDistDB DESC

OPTION(RECOMPILE);

This allows us to see if a specific article is slow on delivery per a given subscriber or to all subscribers (if are more than one subscriber defined).

The MonitorReplicationArticleCommands stored procedure is built on top of the above SELECT statement and is designed to be executed by a monitoring tool.

The procedure accepts three input parameters: @article, @publication and @threshold and returns the number of commands that had crossed the predefined threshold for the specific article and had not yet been delivered to the subscriber.