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_typeFROM     distribution.dbo.MSsubscriptions (NOLOCK)GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type) b ON a.publication_id = b.publication_idINNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_idWHERE   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 @pendingcmdcountend  

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

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

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

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

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

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

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