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.
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%'