Transactional Replication – is it keeping up?

Working with Transactional Replication with many of our SQL Server clients, one of the big questions that we are always asking is replication keeping up? Recently when deleting a few hundred million rows from a large table that is synced with transactional replication, I needed to programmatically check how the distribution was keeping up. The one measure that indicated that things were behind was the subscriber undistributed commands tab in the replication monitor. When the number of commands waiting to be applied was too large, I had to slow down my delete process.

The problem is that the replication monitor dialogs are not the most user friendly, and they take some time to click around and find that is going on. Instead after using the profiler and doing a bunch of searching on replicated tables, I came up with the following query to see if there are undistributed commands that I need to wait for before doing more deletes.

SELECT a.name, 
       s.agent_id, 
	   sum(s.UndelivCmdsInDistDB) as Undelivered,
	   sum(s.DelivCmdsInDistDB) as Delivered
  FROM [distribution].[dbo].[MSdistribution_status] s
 INNER JOIN [distribution].[dbo].[MSdistribution_agents] a ON a.id = s.agent_id
 GROUP BY s.agent_id, a.name;

A very handy way to not backlog replication.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!