create procedure sys.sp_MSenum_replication_status
(
@agent_type int = 0
)
as
begin
set nocount on
declare @anonymous_mask int,
@all_but_misc_agents int,
@all_agents int,
@snapshot_agent int,
@logreader_agent int,
@distrib_agent int,
@merge_agent int,
@queue_agent int,
@misc_agent int
select @anonymous_mask = 0x80000000,
@all_but_misc_agents = -1,
@all_agents = 0,
@snapshot_agent = 1,
@logreader_agent = 2,
@distrib_agent = 3,
@merge_agent = 4,
@queue_agent = 9,
@misc_agent = 5
-- if we want all agents excluding the misc agents then
-- that is the same as requesting all of the agents since
-- in this proc we do not do any work on misc agents
if @agent_type = @all_but_misc_agents
begin
select @agent_type = @all_agents
end
-- SNAPSHOT AGENT STATUS
( select 'publisher' = upper(ss.srvname collate database_default),
'publisher_db' = mssa.publisher_db,
'publication' = mssa.publication,
'publication_type' = mssa.publication_type,
'agent_type' = @snapshot_agent,
'status' = mssh.runstatus,
'agent_id' = mssa.id,
'agent_name' = mssa.name,
'job_id' = cast(mssa.job_id as uniqueidentifier),
'time_stamp' = mssh.time,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSsnapshot_history mssh with (nolock),
dbo.MSsnapshot_agents mssa with (nolock)
where @agent_type in (@all_agents, @snapshot_agent)
and ss.srvid = mssa.publisher_id
and mssh.agent_id = mssa.id
and mssh.timestamp = (select max(timestamp)
from dbo.MSsnapshot_history mssh2 with (nolock)
where mssh2.agent_id = mssa.id)
UNION
select upper(ss.srvname collate database_default),
mssa.publisher_db,
mssa.publication,
mssa.publication_type,
@snapshot_agent,
0,
mssa.id,
mssa.name,
cast(mssa.job_id as uniqueidentifier),
null,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSsnapshot_agents mssa with (nolock)
where @agent_type in (@all_agents, @snapshot_agent)
and ss.srvid = mssa.publisher_id
and not exists (select *
from dbo.MSsnapshot_history mssh with (nolock)
where mssh.agent_id = mssa.id)
)
-- LOGREADER AGENT STATUS
UNION ALL
( select upper(ss.srvname collate database_default),
msla.publisher_db,
'ALL',
0,
@logreader_agent,
mslh.runstatus,
msla.id,
msla.name,
cast(msla.job_id as uniqueidentifier),
mslh.time,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSlogreader_history mslh with (nolock),
dbo.MSlogreader_agents msla with (nolock)
where @agent_type in (@all_agents, @logreader_agent)
and ss.srvid = msla.publisher_id
and mslh.agent_id = msla.id
and mslh.timestamp = (select max(timestamp)
from dbo.MSlogreader_history mslh2 with (nolock)
where mslh2.agent_id = msla.id)
UNION
select upper(ss.srvname collate database_default),
msla.publisher_db,
'ALL',
0,
@logreader_agent,
0,
msla.id,
msla.name,
cast(msla.job_id as uniqueidentifier),
NULL,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSlogreader_agents msla with (nolock)
where @agent_type in (@all_agents, @logreader_agent)
and ss.srvid = msla.publisher_id
and not exists (select *
from dbo.MSlogreader_history mslh with (nolock)
where mslh.agent_id = msla.id)
)
-- DISTRIBUTION AGENT STATUS
UNION ALL
(
-- Normal agents with or without history
select distinct upper(ss.srvname collate database_default),
msda.publisher_db,
msp.publication,
msp.publication_type,
N'agent_type' = @distrib_agent,
N'runstatus' = isnull(msdh.runstatus,0),
msda.id,
msda.name,
cast(msda.job_id as uniqueidentifier),
N'time' = msdh.time,
N'publisher_srvid' = ss.srvid
from dbo.MSdistribution_agents msda with (nolock)
join master.dbo.sysservers ss with (nolock)
on ss.srvid = msda.publisher_id
left outer join dbo.MSdistribution_history msdh with (nolock)
on msda.id = msdh.agent_id
and msdh.timestamp = (select max(timestamp)
from dbo.MSdistribution_history msdh2 with (nolock)
where msdh2.agent_id = msdh.agent_id)
join dbo.MSsubscriptions mss with (nolock)
on mss.agent_id = msda.id
join dbo.MSpublications msp with (nolock)
on msp.publication_id = mss.publication_id
where @agent_type in (@all_agents, @distrib_agent)
and msda.subscriber_id >= 0 -- skip virtual entries
and msda.anonymous_agent_id is null -- pick only normal
UNION ALL
-- Anonymous agents with or without history
-- Anonymous agents will not have entries in MSsubscriptions
select distinct upper(ss.srvname collate database_default),
msda.publisher_db,
msp.publication,
msp.publication_type,
N'agent_type' = @distrib_agent | @anonymous_mask,
N'runstatus' = isnull(msdh.runstatus,0),
msda.id,
msda.name,
cast(msda.job_id as uniqueidentifier),
N'time' = msdh.time,
N'publisher_srvid' = ss.srvid
from dbo.MSdistribution_agents msda with (nolock)
join master.dbo.sysservers ss with (nolock)
on ss.srvid = msda.publisher_id
left outer join dbo.MSdistribution_history msdh with (nolock)
on msda.id = msdh.agent_id
and msdh.timestamp = (select max(timestamp)
from dbo.MSdistribution_history msdh2 with (nolock)
where msdh2.agent_id = msdh.agent_id)
join dbo.MSpublications msp with (nolock)
on msp.publisher_id = msda.publisher_id
and msp.publisher_db = msda.publisher_db
and msp.publication = msda.publication
where @agent_type in (@all_agents, @distrib_agent)
and msda.subscriber_id >= 0 -- skip virtual entries
and msda.anonymous_agent_id is not null -- pick only anonymous
)
-- MERGE AGENT STATUS
UNION ALL
-- all subscription with its agent that has run with latest run status
( select upper(ss.srvname collate database_default),
msma.publisher_db,
msma.publication,
2,
case when msma.anonymous_subid is not null then @merge_agent | @anonymous_mask else @merge_agent end,
msmh.runstatus,
msma.id,
msma.name,
cast(msma.job_id as uniqueidentifier),
null,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSmerge_sessions msmh with (nolock),
dbo.MSmerge_agents msma with (nolock)
where @agent_type in (@all_agents, @merge_agent)
and ss.srvid = msma.publisher_id
and msmh.agent_id = msma.id
and msmh.session_id = (select top 1 msmh2.session_id
from dbo.MSmerge_sessions msmh2 with (nolock)
where msmh2.agent_id = msma.id
order by msmh2.session_id desc)
UNION
-- all subscription with its agent that has not yet run with latest run status=0
select upper(ss.srvname collate database_default),
msma.publisher_db,
msma.publication,
2,
@merge_agent,
0,
msma.id,
msma.name,
cast(msma.job_id as uniqueidentifier),
null,
'publisher_srvid' = ss.srvid
from master.dbo.sysservers ss with (nolock),
dbo.MSmerge_agents msma with (nolock)
where @agent_type in (@all_agents, @merge_agent)
and ss.srvid = msma.publisher_id
and not exists (select *
from dbo.MSmerge_sessions msmh with (nolock)
where msmh.agent_id = msma.id)
)
-- QUEUE READER AGENT STATUS
UNION ALL
select upper(@@servername),
db_name(),
'ALL',
0,
@queue_agent,
msqh.runstatus,
msqa.id,
msqa.name,
cast(msqa.job_id as uniqueidentifier),
msqh.time,
'publisher_srvid' = (select top 1 srvid from master.dbo.sysservers where srvname = @@servername)
from dbo.MSqreader_history msqh with (nolock),
dbo.MSqreader_agents msqa with (nolock)
where @agent_type in (@all_agents, @queue_agent)
and msqh.agent_id = msqa.id
and msqh.timestamp = (select max(msqh2.timestamp)
from dbo.MSqreader_history msqh2 with (nolock)
where msqh2.agent_id = msqa.id)
end
