select [distributionHistory].comments [Comments]
, isnull([Last Error].error_text,'') as [Last Error]
, convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]
, [distributionAgent].publication [Publication Name]
, [distributionAgent].publisher_db as [Publisher Database]
, (case
when [distributionHistory].runstatus = '1' then 'Start'
when [distributionHistory].runstatus = '2' then 'Succeed'
when [distributionHistory].runstatus = '3' then 'InProgress'
when [distributionHistory].runstatus = '4' then 'Idle'
when [distributionHistory].runstatus = '5' then 'Retry'
when [distributionHistory].runstatus = '6' then 'Fail'
else cast([distributionHistory].runstatus as varchar)
end
) [Run Status]
, sub.name [Subscriber]
, [distributionAgent].subscriber_db [Subscriber Database]
, (case
when [distributionAgent].subscription_type = '0' then 'Push'
when [distributionAgent].subscription_type = '1' then 'Pull'
when [distributionAgent].subscription_type = '2' then 'Anonymous'
else cast([distributionAgent].subscription_type as varchar)
end
) [Subscription Type]
, und.UndelivCmdsInDistDB [Undelivered Commands]
from
[distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)
left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)
on [distributionHistory].agent_id = [distributionAgent].id
join
(
select s.agent_id
, MaxAgentValue.[time]
, sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB
from
[distribution].[dbo].[MSrepl_commands] t with (nolock)
join [distribution].[dbo].MSsubscriptions as s with (nolock)
on (
t.article_id = s.article_id
and t.publisher_database_id = s.publisher_database_id
)
join
(
select hist.agent_id
, max(hist.[time]) as [time]
, h.maxseq
from
[distribution].[dbo].MSdistribution_history hist with (nolock)
join
(
select agent_id
, isnull(max(xact_seqno), 0x0) as maxseq
from [distribution].[dbo].MSdistribution_history with (nolock)
group by agent_id
) as h
on (
hist.agent_id = h.agent_id
and h.maxseq = hist.xact_seqno
)
group by
hist.agent_id
, h.maxseq
) as MaxAgentValue
on MaxAgentValue.agent_id = s.agent_id
group by
s.agent_id
, MaxAgentValue.[time]
) und
on [distributionAgent].id = und.agent_id
and und.[time] = [distributionHistory].[time]
join [master].[sys].[servers] as sub with (nolock)
on [distributionAgent].subscriber_id = sub.server_id
outer apply (
select top 1
error_text
from [distribution].[dbo].MSrepl_errors with (nolock)
where id = [distributionHistory].error_id
order by time desc
) as [Last Error];
![Jon Culp [.com]](https://secureservercdn.net/198.71.233.189/cg7.7e8.myftpupload.com/wp-content/uploads/2017/11/sanantonio_wjon3.jpg?time=1652463445)
