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

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

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

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

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

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

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

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