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  

上面的存储过程主要更改3个地方，然后在其他一个数据库运行，页可在distribution运行。

1. 多一个返回参数，@pendingcmdcount int output ，返回未分发的命令数

2. 估计时间不需要，去掉

3. 查询的每个表或内部存储过程加数据库前缀distribution

接下来再用一个存储过程把所有值加起来，给计数器！

新建一个作业，定时每分钟执行，观察性能计数器统计情况。

这样也可以再cacti捕获了！~