The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_replmonitorhelpsubscription(nvarchar @publisher, nvarchar @publisher_db
, nvarchar @publication
, int @publication_type
, int @mode
, int @topnum
, bit @exclude_anonymous
, tinyint @refreshpolicy)
MetaData:
create procedure sys.sp_replmonitorhelpsubscription ( @publisher sysname = NULL -- pubisher - cannot be null for sql server 2008 or before. for SQL 11 or later, means all publishers ,@publisher_db sysname = NULL -- NULL for wildcard entry ,@publication sysname = NULL -- NULL for wildcard entry ,@publication_type int = NULL -- cannot be null in wildcard entry ,@mode int = 0 -- various filter modes ,@topnum int = NULL -- select only top topnum rows ,@exclude_anonymous bit = 0 -- exclude anonymous subscribers ,@refreshpolicy tinyint = 0 -- 0 = default cache refresh, 1 = optimistic force refresh, 2 = non-optimistic force refresh ) as begin set nocount on declare @retcode int ,@curdate datetime -- -- constants -- declare @expiration int ,@latency int ,@distribution_agentname sysname ,@mergeexpirationChosen int ,@mergerundurationChosen int ,@mergestoppedcontinuousagentChosen int ,@mergerunspeedChosen int ,@modeallsubscription tinyint ,@modeinerroronly tinyint ,@modeinwarningonly tinyint ,@modeinerrorandwarningonly tinyint ,@modetop25worstperforming tinyint ,@modetop50worstperforming tinyint ,@modesynchronizing tinyint ,@modenotsynchronizing tinyint -- -- initialize constants -- select @expiration = 1 ,@latency = 2 ,@mergeexpirationChosen=4 ,@mergerundurationChosen=5 ,@mergestoppedcontinuousagentChosen=6 ,@mergerunspeedChosen=7 ,@modeallsubscription = 0 ,@modeinerroronly = 1 ,@modeinwarningonly = 2 ,@modeinerrorandwarningonly = 3 ,@modetop25worstperforming = 4 ,@modetop50worstperforming = 5 ,@modesynchronizing = 6 ,@modenotsynchronizing = 7 -- -- security check : replmonitor -- if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1) begin raiserror(14260, 16, -1) return (1) end -- -- security: Has to be executed from distribution database -- if sys.fn_MSrepl_isdistdb (db_name()) != 1 begin raiserror (21482, 16, -1, 'sp_replmonitorhelpsubscription', 'distribution') return 1 end -- -- validate @publication_type -- if (@publisher_db is NULL or @publication is NULL) and @publication_type is NULL begin raiserror(20587, 16, -1, '@publication_type', 'sp_replmonitorhelpsubscription') return (1) end -- -- validate @mode -- if (@mode not in (@modeallsubscription,@modeinerroronly,@modeinwarningonly,@modeinerrorandwarningonly ,@modetop25worstperforming,@modetop50worstperforming,@modesynchronizing,@modenotsynchronizing)) begin raiserror(20587, 16, -1, '@mode', 'sp_replmonitorhelpsubscription') return (1) end -- -- initialize constants -- select @expiration = 1 ,@latency = 2 ,@curdate = getdate() -- -- create temp table to get the monitoring data -- create table #tmp_replication_monitordata ( publication_id int ,publisher sysname ,publisher_srvid int ,publisher_db sysname ,publication sysname ,publication_type int ,agent_type int ,agent_name sysname ,job_id uniqueidentifier ,status int ,isagentrunningnow bit ,warning int -- latest session's warning ,last_distsync datetime -- last sync time ,agentstoptime datetime -- agent stop time ,distdb sysname null ,retention int ,time_stamp datetime null ,worst_latency int ,best_latency int ,avg_latency int ,cur_latency int ,mergePerformance int ,mergelatestsessionrunduration int ,mergelatestsessionrunspeed float ,mergelatestsessionconnectiontype int ,retention_period_unit tinyint ) if @@error != 0 begin raiserror(20507, 16, 3, '#tmp_replication_monitordata', 'tempdb') return 1 end -- -- build indices -- create nonclustered index nc1tmp_replication_monitordata on #tmp_replication_monitordata(publisher_srvid) create nonclustered index nc2tmp_replication_monitordata on #tmp_replication_monitordata(agent_type) create nonclustered index nc4tmp_replication_monitordata on #tmp_replication_monitordata(publisher) create nonclustered index nc5tmp_replication_monitordata on #tmp_replication_monitordata(publication, publisher_db) create nonclustered index nc6tmp_replication_monitordata on #tmp_replication_monitordata(agent_name) if (@@error != 0) return 1 -- -- Get refresh data -- exec @retcode = sys.sp_replmonitorrefreshdata @publisher=@publisher ,@publisher_db=@publisher_db ,@publication=@publication ,@publication_type=@publication_type ,@refreshpolicy = @refreshpolicy if @@error != 0 or @retcode != 0 return 1 -- -- -- set @publication_type for specific publication query -- and validate -- if (@publication_type is null) begin select @publication_type = publication_type from #tmp_replication_monitordata where upper(publisher) = upper(@publisher) and publisher_db = @publisher_db and publication = @publication end else if (@publication_type not in (0,1,2)) begin raiserror(20587, 16, -1, '@publication_type', 'sp_replmonitorhelpsubscription') return (1) end -- -- Adjust the rows to return -- if (@mode in (@modetop25worstperforming, @modetop50worstperforming)) begin if (@mode = @modetop25worstperforming) select @topnum = case when (isnull(@topnum, 30) > 25) then 25 else @topnum end else select @topnum = case when (isnull(@topnum, 60) > 50) then 50 else @topnum end end -- -- process based on publication_type -- if (@publication_type in (0,1)) begin create table #tmp_subscriptiondata ( -- static publisher_srvid int ,publisher_db sysname ,publication sysname ,publication_id int ,subscriber_id int ,subscriber sysname null ,subscriber_db sysname ,publication_type int -- Type of publication: 0 = Transactional, 1 = Snapshot, 2 = Merge ,subtype int -- Type of subscription: dbo.MSmerge_subscriptions.subscription_type -- 0 = Push, 1 = Pull, 2 = Anonymous ,subscription_time datetime -- dynamic ,status int ,warning int ,monitorranking int ,last_distsync datetime -- tran ,distribution_agentname sysname null ,logreaderagent_status int null ,latency int ,latencythreshold int ,agentnotrunning int ,agentnotrunningthreshold int ,timetoexpiration int ,expirationthreshold int -- merge -- fill mergesubscriptionfriendlyname by dbo.MSmerge_subscriptions.description ,mergesubscriptionfriendlyname sysname null ,mergeconnectiontype int null ,mergeagentname sysname null ,mergeagentlocation sysname null -- among all session of this subscription, depending on latest connection type -- calc average of run_speed among the subset, -- current_run_speed/avg_run_speed ,mergePerformance int null ,mergerunspeed float null -- latest running session ,mergerunduration int null -- latest running session -- Added later - see if we can reorder ,distributionagentjobid binary(16) null ,mergeagentjobid binary(16) null ,distributionagentid int null ,distributionagentprofileid int null ,mergeagentid int null ,mergeagentprofileid int null ,logreaderagentname sysname null -- -only for SQL11 or later ,publisher sysname null ) create clustered index csubscriptiondata on #tmp_subscriptiondata (monitorranking desc) create index nc1subscriptiondata on #tmp_subscriptiondata (distribution_agentname) create index nc2subscriptiondata on #tmp_subscriptiondata (latency desc) create index nc3subscriptiondata on #tmp_subscriptiondata (mergePerformance) create index nc4subscriptiondata on #tmp_subscriptiondata (publisher_srvid, publisher_db, publication_id, subscriber_db, subscriber_id) create index nc5subscriptiondata on #tmp_subscriptiondata (mergeagentname) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- build #tmp_subscriptiondata for tran -- both snaphot and transactional subscriptions will have a distribution agent -- Get the subscription details based on the distribution agent -- insert into #tmp_subscriptiondata ( subscriber_id ,subscriber ,subscriber_db ,publisher_srvid ,publisher_db ,publication ,publication_id ,publication_type ,subtype ,distribution_agentname ,distributionagentjobid ,distributionagentid ,distributionagentprofileid ,publisher ) select distinct s.subscriber_id, case when s.anonymous_subid is not null then -- anonymous subscription upper(s.subscriber_name) -- name is stored in subscriber_name instead of sys.servers else upper(srv.name) end ,s.subscriber_db ,r.publisher_srvid ,r.publisher_db ,r.publication ,r.publication_id ,r.publication_type ,s.subscription_type ,r.agent_name ,s.job_id ,s.id ,s.profile_id ,r.publisher from #tmp_replication_monitordata as r join dbo.MSdistribution_agents as s with (nolock) on r.publisher_srvid = s.publisher_id and r.publisher_db = s.publisher_db collate database_default and (r.publication = s.publication collate database_default or s.publication = 'ALL' collate database_default) and s.subscriber_id >= 0 -- skip the virtual subscriptions and (r.agent_type & 3) = 3 -- we select the distribution agent entries to optimize and (@exclude_anonymous = 0 or s.anonymous_subid is null) -- anonymous and r.job_id = cast(s.job_id as uniqueidentifier) join sys.servers as srv on srv.server_id = s.subscriber_id -- -- get the latest subscription_time for the subscription to each publication -- update #tmp_subscriptiondata set subscription_time = (select max(s.subscription_time) from dbo.MSsubscriptions as s with (nolock) where subscriber_id = s.subscriber_id and subscriber_db = s.subscriber_db and publisher_srvid = s.publisher_id and publisher_db = s.publisher_db and publication_id = s.publication_id and subtype = s.subscription_type) -- -- The logreader agent status needs to incorporated in the subscription status -- update #tmp_subscriptiondata set logreaderagent_status = isnull(r.status,0) ,logreaderagentname = r.agent_name from #tmp_subscriptiondata as s join #tmp_replication_monitordata as r on s.publisher_srvid = r.publisher_srvid and s.publisher_db = r.publisher_db and r.agent_type=2 and s.publication_type = 0 -- -- set the status of the subscription based on the type of -- publication and agents status that are involved -- populate the threshold values -- populate other fields from #tmp_replication_monitordata -- update #tmp_subscriptiondata set expirationthreshold = cast(sys.fn_replgetpublicationthreshold(r.publication_id, @expiration) as int) ,latencythreshold = cast(sys.fn_replgetpublicationthreshold(r.publication_id, @latency) as int) ,warning = r.warning ,status = case -- when snapshot publication - use distribution agent status when (r.publication_type = 1) then r.status -- else we have transactional publication else case -- Error = when any one agent has error when (r.status = 6 or logreaderagent_status = 6) then 6 -- Retry = when any one agent has retry when (r.status = 5 or logreaderagent_status = 5) then 5 -- Stopped = when any one agent has stopped when (r.status = 2 or logreaderagent_status = 2) then 2 -- Idle (Running) = when both agents are idle when (r.status = 4 and logreaderagent_status = 4) then 4 -- Inprogress (Running) = when any one agent is in progress and the other is still running when (r.status = 3 and logreaderagent_status in (3, 4) or logreaderagent_status = 3 and r.status in (3, 4)) then 3 -- Startup (Running) = when any one agent is in startup and the other is still running when (r.status = 1 and logreaderagent_status in (1, 3, 4) or logreaderagent_status = 1 and r.status in (1, 3, 4)) then 1 -- we should not come here else 0 end end ,latency = r.cur_latency ,agentnotrunning = case when (r.agentstoptime is null) then null else datediff(hour, r.agentstoptime, @curdate) end ,last_distsync = r.last_distsync ,timetoexpiration = case when (r.retention is null) then null else datediff(hour, @curdate, dateadd(hour, r.retention, subscription_time)) end from #tmp_replication_monitordata as r where r.agent_name = distribution_agentname -- -- Set the monitor ranking based on status, warning, publication_type -- update #tmp_subscriptiondata set monitorranking = sys.fn_replmonitorsubscriptionranking(status, warning, publication_type) -- -- set the rowcount if necessary -- if @topnum is not null set rowcount @topnum -- -- return the rowset based on mode -- select status ,warning ,subscriber ,subscriber_db ,publisher_db ,publication ,publication_type ,subtype ,latency ,latencythreshold ,agentnotrunning ,agentnotrunningthreshold ,timetoexpiration ,expirationthreshold ,last_distsync ,distribution_agentname ,mergeagentname ,mergesubscriptionfriendlyname ,mergeagentlocation ,mergeconnectiontype ,mergePerformance ,mergerunspeed ,mergerunduration ,monitorranking ,distributionagentjobid ,mergeagentjobid ,distributionagentid ,distributionagentprofileid ,mergeagentid ,mergeagentprofileid ,logreaderagentname ,publisher from #tmp_subscriptiondata where @mode in (@modeallsubscription, @modetop25worstperforming, @modetop50worstperforming) -- return all subscriptions or (@mode = @modeinerroronly and monitorranking = 60) -- return only ones in error or (@mode = @modeinwarningonly and monitorranking between 50 and 59 ) -- return only ones with warning or (@mode = @modeinerrorandwarningonly and monitorranking between 50 and 60 ) -- return only ones with warning or error or (@mode = @modesynchronizing and status in (1,3,4)) -- return only ones with running agents or (@mode = @modenotsynchronizing and status not in (1,3,4)) -- return only ones with stopped agents order by monitorranking desc -- highest rank will be shown first ,latency desc -- highest latency first end else begin -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- handle merge subscriptions declare @publisher_id smallint select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default -- -- set the rowcount if necessary -- if @topnum is not null set rowcount @topnum -- -- return the rowset based on mode -- select sub.status ,sub.warning ,sub.subscriber ,sub.subscriber_db ,sub.publisher_db ,sub.publication ,sub.publication_type ,sub.subtype ,sub.latency ,sub.latencythreshold ,sub.agentnotrunning ,sub.agentnotrunningthreshold ,timetoexpiration = case when (sub.retention is null) then null else (case when sub.retention_period_unit = 0 then datediff(hour, @curdate, dateadd(hour, sub.retention, sub.last_distsync)) else (datediff(hour, @curdate, sys.fn_add_units_to_date(sub.retention, sub.retention_period_unit, sub.last_distsync))) end) end ,sub.expirationthreshold ,sub.last_distsync ,sub.distribution_agentname ,sub.mergeagentname ,sub.mergesubscriptionfriendlyname ,sub.mergeagentlocation ,sub.mergeconnectiontype ,sub.mergePerformance ,sub.mergerunspeed ,sub.mergerunduration ,sub.monitorranking ,sub.distributionagentjobid ,sub.mergeagentjobid ,sub.distributionagentid ,sub.distributionagentprofileid ,sub.mergeagentid ,sub.mergeagentprofileid ,sub.logreaderagentname ,sub.publisher from ( select -- static subscriber = upper(agents.subscriber_name) ,subscriber_id = agents.subscriber_id ,subscriber_db = agents.subscriber_db ,publisher_srvid = r.publisher_srvid ,publisher_db = r.publisher_db ,publication = r.publication ,publication_id = r.publication_id ,publication_type = r.publication_type ,subtype = case when agents.anonymous_subid is not null then 2 else s.subscription_type end -- dynamic ,status = r.status ,warning = r.warning ,agentnotrunning = case when (r.agentstoptime is null) then null else datediff(hour, @curdate, r.agentstoptime) end ,last_distsync = r.last_distsync ,subscription_time = s.subscription_time -- merge perf ,mergeagentname = agents.name -- ,'agentname' ,mergeagentlocation = case when s.subscription_type=0 then @publisher else UPPER(agents.subscriber_name) end ,mergesubscriptionfriendlyname = s.description ,mergeconnectiontype = mergelatestsessionconnectiontype ,mergerunduration = r.mergelatestsessionrunduration -- 2,NULL ,mergerunspeed=r.mergelatestsessionrunspeed ,mergePerformance = r.mergePerformance ,mergeagentjobid = agents.job_id ,mergeagentid = agents.id ,mergeagentprofileid = agents.profile_id ,retention = r.retention ,retention_period_unit = r.retention_period_unit ,monitorranking = case when (r.status > 5 or r.warning > 0) then sys.fn_replmonitorsubscriptionranking(r.status, r.warning, r.publication_type) else (case when r.status = 0 then 50 else (case when r.status = 5 then 40 else (case when r.status = 2 then 20 else 30 end) end) end) end ,latency = NULL ,latencythreshold = NULL ,agentnotrunningthreshold = NULL ,distribution_agentname = NULL ,distributionagentjobid = NULL ,distributionagentid = NULL ,distributionagentprofileid = NULL ,logreaderagentname = NULL ,expirationthreshold = NULL ,r.publisher from #tmp_replication_monitordata as r join dbo.MSmerge_agents agents with (nolock) on agents.name = r.agent_name collate database_default and (@exclude_anonymous = 0 or agents.anonymous_subid is null) and ((@publisher is null and agents.publisher_id is not null) or (agents.publisher_id=@publisher_id)) and agents.publisher_db=r.publisher_db collate database_default and agents.publication=r.publication collate database_default and (r.agent_type & 4) = 4 -- for merge only left outer join dbo.MSmerge_subscriptions as s with (nolock) on r.publisher_srvid = s.publisher_id and r.publisher_db = s.publisher_db collate database_default and r.publication_id = s.publication_id and s.subscriber is not NULL -- skip the virtual entries in dbo.MSsubscriptions and UPPER(agents.subscriber_name)=UPPER(s.subscriber) and agents.subscriber_db=s.subscriber_db ) as sub where @mode = @modeallsubscription -- return all subscriptions or (@mode in (@modetop25worstperforming, @modetop50worstperforming) and sub.mergePerformance is not null) -- return worst 25/50 subscriptions based on perf. or (@mode = @modeinerroronly and sub.monitorranking = 60) -- return only ones in error or (@mode = @modeinwarningonly and sub.monitorranking between 50 and 59 ) -- return only ones with warning or (@mode = @modeinerrorandwarningonly and sub.monitorranking between 50 and 60 ) -- return only ones with warning or error or (@mode = @modesynchronizing and sub.status in (1,3,4)) -- return only ones with running agents or (@mode = @modenotsynchronizing and sub.status not in (1,3,4)) -- return only ones with stopped agents order by sub.monitorranking desc -- highest rank will be shown first ,sub.mergePerformance asc -- lowest mergePerformance first end -- -- all done -- return 0 end
No comments:
Post a Comment