| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

T-SQL Get Replication Distribution History Errors
by BF (Principal Consultant; Architecture; Engineering)

A T-SQL Query to show all Distribution Agents and all associated errors, with aggregrated counts, over a specified time interval.
The script runs against your Distribution Database and assumes all Distribution Agents are local on the Server.


Declare @TimeOffSetHours int = -8
Select t2.Name, t1.Agent_id, t1.Comments, count(*) Total
From Distribution.dbo.MSdistribution_history t1
Inner Join
Distribution.dbo.MSdistribution_agents t2 on
Where t1.error_id is not null and t1.error_id <> '' --where error_id is present
And t1.[time] > DATEADD(HOUR, @TimeOffSetHours, GETDATE()) --Last x hours
Group By, t1.agent_id, t1.comments
Order By Total Desc

Select * from MSdistribution_history Order By [time] Desc
Select * from MSdistribution_agents Order By Name Asc
Select * from MSdistribution_history where agent_id=1353 Order By [time] Desc