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