June 7, 2012

sp_replmonitorsubscriptionpendingcmds (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_replmonitorsubscriptionpendingcmds(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @subscription_type)

MetaData:

 create procedure sys.sp_replmonitorsubscriptionpendingcmds   
(
@publisher sysname -- cannot be null
,@publisher_db sysname -- cannot be null
,@publication sysname -- cannot be null
,@subscriber sysname -- cannot be null
,@subscriber_db sysname -- cannot be null
,@subscription_type int
)
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@publisher_id int
,@subscriber_id int
,@lastrunts timestamp
,@avg_rate float
,@xact_seqno varbinary(16)
,@inactive int = 1
,@virtual int = -1

--
-- PAL security check done inside sp_MSget_repl_commands
-- security: Has to be executed from distribution database
--
if sys.fn_MSrepl_isdistdb (db_name()) != 1
begin
raiserror (21482, 16, -1, 'sp_replmonitorsubscriptionpendingcmds', 'distribution')
return 1
end
--
-- validate @subscription_type
--
if (@subscription_type not in (0,1))
begin
raiserror(14200, 16, 3, '@subscription_type')
return 1
end
--
-- get the server ids for publisher and subscriber
--
select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher)
if (@publisher_id is null)
begin
raiserror(21618, 16, -1, @publisher)
return 1
end
select @subscriber_id = server_id from sys.servers where upper(name) = upper(@subscriber)
if (@subscriber_id is null)
begin
raiserror(20032, 16, -1, @subscriber, @publisher)
return 1
end
--
-- get the agent id
--
select @agent_id = id
from dbo.MSdistribution_agents
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication in (@publication, 'ALL')
and subscriber_id = @subscriber_id
and subscriber_db = @subscriber_db
and subscription_type = @subscription_type
if (@agent_id is null)
begin
raiserror(14055, 16, -1)
return (1)
end;
--
-- Compute timestamp for latest run
--
with dist_sessions (start_time, runstatus, timestamp)
as
(
select start_time, max(runstatus), max(timestamp)
from dbo.MSdistribution_history
where agent_id = @agent_id
and runstatus in (2,3,4)
group by start_time
)
select @lastrunts = max(timestamp)
from dist_sessions;
if (@lastrunts is null)
begin
--
-- Distribution agent has not run successfully even once
-- and virtual subscription of immediate sync publication is inactive (snapshot has not run), no point of returning any counts
-- see SQLBU#320752, orig fix SD#881433, and regression bug VSTS# 140179 before you attempt to fix it differently :)
if exists (select *
from dbo.MSpublications p join dbo.MSsubscriptions s on p.publication_id = s.publication_id
where p.publisher_id = @publisher_id
and p.publisher_db = @publisher_db
and p.publication = @publication
and p.immediate_sync = 1
and s.status = @inactive and s.subscriber_id = @virtual)
begin
select 'pendingcmdcount' = 0, N'estimatedprocesstime' = 0
return 0
end
--
-- Grab the max timestamp
--
select @lastrunts = max(timestamp)
from dbo.MSdistribution_history
where agent_id = @agent_id
end
--
-- get delivery rate for the latest completed run
-- get the latest sequence number
--
select @xact_seqno = xact_seqno
,@avg_rate = delivery_rate
from dbo.MSdistribution_history
where agent_id = @agent_id
and timestamp = @lastrunts
--
-- if no rows are selected in last query
-- explicitly initialize these variables
--
select @xact_seqno = isnull(@xact_seqno, 0x0)
,@avg_rate = isnull(@avg_rate, 0.0)
--
-- if we do not have completed run
-- get the average for the agent in all runs
--
if (@avg_rate = 0.0)
begin
select @avg_rate = isnull(avg(delivery_rate),0.0)
from dbo.MSdistribution_history
where agent_id = @agent_id
end
--
-- get the count of undelivered commands
-- PAL check done inside
--
DECLARE @countab TABLE ( pendingcmdcount int )
insert into @countab (pendingcmdcount)
exec @retcode = sys.sp_MSget_repl_commands
@agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000
if (@retcode != 0 or @@error != 0)
return 1
--
-- compute the time to process
-- return the resultset
--
select
pendingcmdcount
,N'estimatedprocesstime' = case when (@avg_rate != 0.0)
then cast((cast(pendingcmdcount as float) / @avg_rate) as int)
else pendingcmdcount end
from @countab
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews