Monitoring Replication

Is replication working? It’s a question I often get asked, and it’s sometimes hard to even answer without digging into the depths of SQL server replication. The quickest way to check is firstly to fire up replication monitor and have a look in there.

If you are greeted with a bunch of red x’s then obviously you need to be looking into it. Also you will see exclamation marks if things are working but there is a lot of latency.

Some  applications require almost real time replication for them to work properly.

Using replication monitor is fine for the most part.. If you spot a problem you can dig into it and see whether the problem is between the publisher and distributor, or between the distributor and subscriber.

However sometimes all these things look ok, or maybe there are lots of exclamation marks but you want to check that everything is being delivered and that is where the below query can help. I found this at the below link.

http://www.sql-server-performance.com/2011/transactional-replication-distribution-queue/

Run the below on the distribution database.

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%'
Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.