SqlServer 监控发布中未分发的命令数

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

原文:SqlServer 监控发布中未分发的命令数

对于查看未分发的命令数，我们通常这样查看。

然而当服务器有很多发布时，一个个打开查看就很麻烦

当然，如果想用脚本查看就更方便了，运行下面的语句

--查看各发布订阅未分发的命令数和估计时间
SELECT 'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N'''
+ a.publisher + ''', @publisher_db = N''' + a.publisher_db
+ ''', @publication = N''' + a.publication + ''', @subscriber = N'''
+ c.name + ''', @subscriber_db = N''' + b.subscriber_db
+ ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR)
FROM distribution.dbo.MSreplication_monitordata a ( NOLOCK )
INNER JOIN (
SELECT publication_id ,subscriber_id ,subscriber_db ,subscription_type
FROM distribution.dbo.MSsubscriptions (NOLOCK)
GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type
) b ON a.publication_id = b.publication_id
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1

上面自动生成各个发布未分发的命令的存储过程，执行就能查看各个发布等待分发的命令和估计时间：

EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds
@publisher = N'发布服务器'
, @publisher_db = N'发布数据库'
, @publication = N'发布名称'
, @subscriber = N'订阅服务器'
, @subscriber_db = N'订阅数据库'
, @subscription_type =0

/***************************************************************************/
/***************************************************************************/

但是，由于监控需要，只想统计所有发布未分发的命令数“ pendingcmdcount”的总和，

本来想用下面这个方法把各个存储过程的返回值插入到临时表，但是没有这种方法！！

CREATE TABLE #countab(pendingcmdcount int,estimatedprocesstime int)INSERT INTO #countab(pendingcmdcount,estimatedprocesstime)
EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds
@publisher = N'发布服务器'
, @publisher_db = N'发布数据库'
, @publication = N'发布名称'
, @subscriber = N'订阅服务器'
, @subscriber_db = N'订阅数据库'
, @subscription_type =0

上面这个方法也可行，但是只在本地的测试可行。就是创建链接服务器，加到数据库distribution前面。

实际服务器有本地的链接服务器，但是没显示出来，也用不了。虽然可以创建其他命名的，但不想创建太多链接服务器。

于是想到更改系统的存储过程！！~错了，是参考系统存储过程创建一个几乎一样定义的存储过程。

即不需要distribution.sys.sp_replmonitorsubscriptionpendingcmds ，创建类似的dbo.sp_getsubscriptionpendingcmds ，如下

create procedure dbo.sp_getsubscriptionpendingcmds
(
@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
,@pendingcmdcount int output
)
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@publisher_id int
,@subscriber_id int
,@lastrunts timestamp
,@xact_seqno varbinary(16) ,@inactive int = 1
,@virtual int = -1 select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher)
select @subscriber_id = server_id from sys.servers where upper(name) = upper(@subscriber) select @agent_id = id
from distribution.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 ;with dist_sessions (start_time, runstatus, timestamp)
as(
select start_time, max(runstatus), max(timestamp)
from distribution.dbo.MSdistribution_history
where agent_id = @agent_id
group by start_time
)
select @lastrunts = max(timestamp) from dist_sessions where runstatus in (2,3,4); if (@lastrunts is null)
begin
if exists (
select * from distribution.dbo.MSpublications p
inner join distribution.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 select @lastrunts = max(timestamp) from distribution.dbo.MSdistribution_history where agent_id = @agent_id
end select @xact_seqno = xact_seqno from distribution.dbo.MSdistribution_history where agent_id = @agent_id and timestamp = @lastrunts select @xact_seqno = isnull(@xact_seqno, 0x0) DECLARE @countab TABLE ( pendingcmdcount int )
insert into @countab (pendingcmdcount)
exec @retcode = distribution.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 select @pendingcmdcount=pendingcmdcount from @countab return @pendingcmdcount
end