Check Transactional Replication Status

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];

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]

Check Transactional Replication Status

Leave a Reply Cancel reply

An Information Archive for a Frivolous Human