Troubleshooting SQL Server replication delays

When dealing with replication in a large environment you may sometimes come across delays in replication. The users will be complaining the data is not there, and the service desk/support analysts will be coming to you to find out whats going on with it.

Often looking inside replication monitor can be confusing at the best of times, you will see messages like..

You may be able to gain more insight into what exactly is going on by running the below on the Published database. This command will show you the pending replication commands waiting to be sent to the distributor. If the transaction log is full on the publisher this can be a common reason for things not moving along.

-- run this on publisher to show what is qeueing
sp_repltrans

You will then want to see what is queing at the distributor and the below code will show you the commands waiting to be applied to the subscriber.

--run this on distribution server to show whats waiting to go from the distributer to the 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%'

Once you have found out where the delays are and confirmed whether or not things are actually moving along you can use the activity monitor, profiler etc to find out where the holdup is.

Sometimes the best thing to do if nothing obvious is wrong, is just to be patient and wait. Go and have a cup of coffee, come back and see how things are moving along.

The worst thing you can do is panic, and start sending snapshots across which could take the database down for hours.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

3 Comments

  1. Hello ,

    Is there any way we can identify current commands which were already delivered to subscriber because MSDistribution_Status. view gives cumulative numbers of delivered commands.
    I need to know present commands which are already delivered to sub.
    any help would be greatly appreciated.
    Thank You,
    Vaibhav

    1. Hey Vaibhav, sorry I haven’t touched replication for a few years now as the companies I have been working with don’t use it. Sorry I don’t have any more help on that question. Good luck though and feel free to pop back and share the answer when you find it. Thanks

Leave a Reply

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