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.
var idcomments_acct = ’62089e20c30196b091933230f42f0718′;var idcomments_post_id;var idcomments_post_url;