info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL Get Replication Distribution History Errors
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-11-04








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.


T-SQL:

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 t1.agent_id=t2.id
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 t2.name, 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