Troubleshooting Transactional Replication Distribution Agent Latency


Here is a tip I found while troubleshooting a Distribution Agent latency problem.  We were trying to see why on some days the Distribution Agent was “getting behind”.  We suspected it was volume related.  Below are a few queries to help uncover workload in the Distribution database.


The query below returns count of transactions.  It’s not fancy, but it helps identify days with higher then average counts.


–Return count of transactions by day by publication


select datepart(dayofyear,entry_time) as ‘day of year’,


publisher_database_id as ‘pub db id’,


count(*) as ‘count of trans’ from MSrepl_transactions


where entry_time < getdate() 5 –count trans older then 5 days


group by datepart(dayofyear,entry_time), publisher_database_id


order by datepart(dayofyear,entry_time), publisher_database_id


 


day of year pub db id   count of trans


———– ———– ————–


302         7           2291


303         7           319369


304         7           420476


305         7           4633313


306         7           411236


307         7           459866


308         7           755705


309         7           937608


310         1           57326


310         2           18950


310         7           462553


310         8           122002


311         1           38203


311         2           24254


311         7           257801


311         8           105436


–Chris Skorlinski


Comments (0)

Skip to main content