1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
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]; |