[SQL Server]自动监控“发布订阅”的滞后时间(延迟时间)

    技术2022-05-13  0

    在发布服务器上,执行如下代码:

    DECLARE @tokenID int;

    -- 在发布项目中,插入一个跟踪令牌(过期时间和其它事务一样,过期后会自动删除)

    EXEC UserCenter.sys.sp_posttracertoken  -- 执行的数据库,为发布项目对应的数据库

      @publication = 'UserCenter',            -- 发布项目名

      @tracer_token_id = @tokenID OUTPUT;    -- 返回一个令牌id

    print @tokenID  

     

    -- 过一小段时间后,根据这个令牌id, 查询一下跟踪结果.

    EXEC UserCenter.sys.sp_helptracertokenhistory

          @publication = 'UserCenter',

     @tracer_id = @tokenID ;

     

    返回结果如下:

    列名

    数据类型

    说明

    distributor_latency

    bigint

    发布服务器提交至“分发服务器”延迟秒数

    subscriber

    sysname

    接收跟踪令牌的订阅服务器的名称。

    subscriber_db

    sysname

    在其中插入跟踪令牌记录的订阅数据库的名称。

    subscriber_latency

    bigint

    分发服务器 至 订阅服务器 延迟的秒数

    overall_latency

    bigint

    总延迟的秒数.

     

     

     


    create table t_config_publication(

        dbname              varchar(50) primary key,  -- 发布项目名称,同时也必须是发布数据库的名称

        is_enable           int                       -- 1:打开, 0关闭

    );

    create table t_log_publication(

        writetime           datetime    not null,    --写日志时间(设置发布跟踪的时间)

        dbname              varchar(50) not null,    --发布项目名称,同时也必须是发布数据库的名称

        token_id            int unique,              --跟踪令牌号

        distributor_latency bigint,                  --发布至分发滞后时间(秒数)

        subscriber_latency  bigint,                  --分发至订阅滞后时间(秒数)

        overall_latency     bigint                   --总滞后秒数

    );

    create clustered index icx_log_publication on t_log_publication(writetime);

     

    create procedure p_job_monitor_publication

    as

    begin

        set nocount on

        declare @dbname   varchar(50),

                @procname varchar(100),

                @token_id int;

     

        create table #tmp_job_monitor_publication(

            distributor_latency bigint,                  -- 发布至分发滞后时间(秒数)

            subscriber          varchar(200),            -- 订阅服务器名称

            subscriber_db       varchar(200),            -- 订阅数据库

            subscriber_latency  bigint,                  -- 分发至订阅滞后时间(秒数)

            overall_latency     bigint                   -- 总滞后秒数

        );

     

        -- 获取最近小时内插入的跟踪的结果

        declare t_cur1 cursor for

        select [dbname],token_id  from t_log_publication with(nolock)

         where writetime > dateadd(hour, -2, getdate()) and distributor_latency is null;

        open t_cur1;

        fetch next from t_cur1 into @dbname, @token_id;

        while (@@fetch_status = 0)

        begin

          -- 获取滞后时间

          truncate table #tmp_job_monitor_publication;

            set @procname = @dbname+'.sys.sp_helptracertokenhistory';

            insert into #tmp_job_monitor_publication

            execute @procname @publication=@dbname, @tracer_id=@token_id;

     

            -- 更新到日志中

            update t_log_publication set distributor_latency = b.distributor_latency, subscriber_latency = b.subscriber_latency,

                                     overall_latency = b.overall_latency

              from t_log_publication a, #tmp_job_monitor_publication b

             where a.token_id = @token_id and b.distributor_latency is not null;

            fetch next from t_cur1 into @dbname, @token_id;

        end

        close t_cur1;

        deallocate t_cur1;

     

        -- 插入一个新的跟踪

        declare t_cur2 cursor for

        select [dbname]  from t_config_publication with(nolock) where is_enable > 0;

        open t_cur2;

        fetch next from t_cur2 into @dbname;

        while (@@fetch_status = 0)

        begin

            set @procname = @dbname+'.sys.sp_posttracertoken';

            execute @procname @publication=@dbname, @tracer_token_id = @token_id OUTPUT;

     

            insert into t_log_publication(dbname, token_id, writetime)

            values(@dbname, @token_id, getdate())

            fetch next from t_cur2 into @dbname;

        end

        close t_cur2;

        deallocate t_cur2;

     

        --删除天前过期的监控数据

        delete from t_log_publication where writetime < dateadd(day, -15, getdate());

     

        set nocount off

    end

    go

     

     

     

     

     

     

     

     


    最新回复(0)