June 7, 2012

sp_replmonitorhelppublication (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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_replmonitorhelppublication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @publication_type
, tinyint @refreshpolicy)

MetaData:

 create procedure sys.sp_replmonitorhelppublication   
(
@publisher sysname = NULL -- pubisher - cannot be null for sql 2008 or before, for sql 11 or later null means all publisher
,@publisher_db sysname = NULL -- NULL for wildcard entry
,@publication sysname = NULL -- NULL for wildcard entry
,@publication_type int = NULL -- NULL for wildcard entry
,@refreshpolicy tinyint = 0 -- 0 = default cache refresh, 1 = optimistic force refresh, 2 = non-optimistic force refresh
)
as
begin
set nocount on
set ansi_warnings off
declare @retcode int
--
-- constants
--
declare @status int
,@warning int
,@worst_latency int
,@best_latency int
,@average_latency int
,@last_distsync datetime
,@retention int
,@subscriptioncount int
,@runningdistagentcount int
,@snapshot_agentname sysname
,@logreader_agentname sysname
,@qreader_agentname sysname
,@expiration int
,@latency int
,@worst_runspeedPerf int
,@best_runspeedPerf int
,@average_runspeedPerf int

--
-- initialize constants
--
select @expiration = 1
,@latency = 2

--
-- warning bitmap definition
Expiration 1 Threshold to warn expiration of subscription to a transactional publication - percentage of retention in hours
latency 2 The time taken to replicate data from the transactional publisher (committed) to the subscriber (committed) - in seconds
mergeexpiration 4 Threshold to warn expiration of merge subscription to a publication - percentage of retention in hours
mergerunspeedDUN 8 Merge delivery rate - in rows/seconds for slow connection
mergerunspeedLAN 16 Merge delivery rate - in rows/seconds for fast connection
mergerundurationDUN 32 The time taken to finish one merge run - in seconds for slow connection
mergerundurationLAN 64 The time taken to finish one merge run - in seconds for fast connection.
--

create table #tmp_publicationdata
(
-- internal use
publication_id int
,distribution_db sysname
-- static
,publisher_db sysname
,publication sysname
,publication_type int
-- dynamic
,status int -- publication status defined as max(status) among all agents
,subscriptioncount int -- # of subscription
,runningdistagentcount int -- # of running agents
,last_distsync datetime -- last sync time
,warning int -- publication warning defined as max(isnull(warning,0)) among all agents
-- TODO: change semantics
,retention int -- retention period
-- tran specific
,snapshot_agentname sysname null
,logreader_agentname sysname null
,qreader_agentname sysname null
,worst_latency int
,best_latency int
,average_latency int
,latencythreshold int
,expirationthreshold int
,agentnotrunningthreshold int
,worst_runspeedPerf int
,best_runspeedPerf int
,average_runspeedPerf int
,retention_period_unit tinyint
-- for SQL 11 or later only if user specifies null for publisher name
,publisher sysname null
)
create clustered index cpublicationdata on #tmp_publicationdata (publication, publication_type, publisher_db)

--
-- 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_replmonitorhelppublication', 'distribution')
return 1
end
--
-- 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
,last_distsync datetime
,agentstoptime datetime
,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, 2, '#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

--
-- build #tmp_publicationdata
-- skip entries for publication with name = ALL
--
insert into #tmp_publicationdata
(
distribution_db
,publisher_db
,publication
,publication_id
,publication_type
,publisher
)
select distinct
distdb
,publisher_db
,publication
,publication_id
,publication_type
,publisher
from #tmp_replication_monitordata
where publication != N'ALL'
--
-- Now update the other columns
--
declare #hcrefreshmonitor cursor local fast_forward for
select publisher_db
,publication
,publication_type
from #tmp_publicationdata
open #hcrefreshmonitor
fetch #hcrefreshmonitor into @publisher_db, @publication, @publication_type
while (@@fetch_status != -1)
begin
--
-- get the values from all agents
-- special publication name = ALL for logreader agent
--
select @status = max(status)
,@warning = max(isnull(warning,0))
,@worst_latency = max(worst_latency)
,@best_latency = min(best_latency)
,@average_latency = cast(avg(cast(avg_latency as bigint)) as int)
,@last_distsync = max(last_distsync)
,@retention = max(retention)
,@worst_runspeedPerf = min(mergePerformance)
,@best_runspeedPerf = max(mergePerformance)
,@average_runspeedPerf = avg (mergePerformance)
from #tmp_replication_monitordata
where publisher_db = @publisher_db
and (publication = @publication or publication = 'ALL')
and publication_type = @publication_type
--
-- subscriptioncount
-- count the number dist/merge agents for the publication
--
-- @agent_type = 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
select @subscriptioncount = isnull(count(agent_name),0)
from #tmp_replication_monitordata
where publisher_db = @publisher_db
and publication = @publication
and publication_type = @publication_type
and ((agent_type & 3) = 3 or (agent_type & 4) = 4)
--
-- runningdistagentcount
-- count the number dist agents for the publication
-- which are running now
--
select @runningdistagentcount = isnull(count(agent_name),0)
from #tmp_replication_monitordata
where publisher_db = @publisher_db
and publication = @publication
and publication_type = @publication_type
and (agent_type & 3) = 3
and isagentrunningnow = 1
--
-- Get the agent names
--
-- join this with MSsnapshot_agents to make sure that we are getting the agent name
-- of the regular snapshot agent and not the dynamic snapshot agent
select @snapshot_agentname = t.agent_name
from #tmp_replication_monitordata t
inner join MSsnapshot_agents a on a.publisher_db = t.publisher_db collate database_default and
a.publication = t.publication collate database_default and
a.publisher_id = t.publisher_srvid and
a.publication_type = t.publication_type and
a.name = t.agent_name collate database_default
where t.publisher_db = @publisher_db
and t.publication = @publication
and t.publication_type = @publication_type
and t.agent_type = 1
and a.dynamic_filter_login is NULL
and a.dynamic_filter_hostname is NULL
--
-- logreader agent is per publisher_db
--
select @logreader_agentname = agent_name
from #tmp_replication_monitordata
where publisher_db = @publisher_db
and agent_type = 2
--
-- if this is a queued publication
-- list the queue reader agent
--
if exists (select * from dbo.MSpublications with (nolock)
where publisher_db = @publisher_db
and publication = @publication
and publication_type = @publication_type
and allow_queued_tran = 1)
begin
select @qreader_agentname = agent_name
,@status = case when status > @status then status else @status end
from #tmp_replication_monitordata
where agent_type = 9
end
--
-- update now
--
update #tmp_publicationdata
set status = @status
,warning = @warning
,worst_latency = @worst_latency
,best_latency = @best_latency
,average_latency = @average_latency
,last_distsync = @last_distsync
,retention = @retention
,subscriptioncount = @subscriptioncount
,runningdistagentcount = @runningdistagentcount
,snapshot_agentname = @snapshot_agentname
,logreader_agentname = @logreader_agentname
,qreader_agentname = @qreader_agentname
,worst_runspeedPerf = @worst_runspeedPerf
,best_runspeedPerf = @best_runspeedPerf
,average_runspeedPerf = @average_runspeedPerf
where publisher_db = @publisher_db
and publication = @publication
and publication_type = @publication_type
--
-- get next row
--
fetch #hcrefreshmonitor into @publisher_db, @publication, @publication_type
end
close #hcrefreshmonitor
deallocate #hcrefreshmonitor
--
-- populate the threshold values
--
update #tmp_publicationdata
set expirationthreshold = cast(sys.fn_replgetpublicationthreshold(publication_id, @expiration) as int)
,latencythreshold = cast(sys.fn_replgetpublicationthreshold(publication_id, @latency) as int)
--
-- select the result set
--
select
publisher_db
,publication
,publication_id
,publication_type
,status
,warning
,worst_latency
,best_latency
,average_latency
,last_distsync
,retention
,latencythreshold
,expirationthreshold
,agentnotrunningthreshold
,subscriptioncount
,runningdistagentcount
,snapshot_agentname
,logreader_agentname
,qreader_agentname
,worst_runspeedPerf
,best_runspeedPerf
,average_runspeedPerf
,retention_period_unit
,publisher
from #tmp_publicationdata
order by publisher, publisher_db, publication, publication_type
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews