代码改变世界

脚本监控sqlserver 2008 R2 replication性能

2012-02-09 10:33  java ee spring  阅读(316)  评论(0编辑  收藏  举报
 

脚本在分发服务器上执行!我主要使用来监控复制情况.下面是个案例,仅供参考!根据实际情况修改相关参数

 

/*
说明:命令在主用用于监控replication性能:
1.分发服务器上执行:监控复制进程是否运行、发布服务器到分发服务器、分发到订阅服务器传递的命令数;
2.分发服务器到订阅服务器复制等待情况

*/

--**********************************************************************************************
--第一部分:监控复制进程是否运行、发布服务器到分发服务器、分发到订阅服务器传递的命令数
--**********************************************************************************************


set nocount on

--新建临时表
create   table  #result
(
dbname sysname  null,
name nvarchar(100)  not null,
status int NOT NULL,
publisher sysname   null,
publisher_db sysname   null,
publication sysname  null,
start_time datetime  null,
time datetime  null,
duration int NULL,
comments nvarchar(255) NULL,
delivered_transactions int NULL,
delivered_commands int NULL,
delivery_rate int NULL,
job_id varchar(36) NULL,
delivery_latency int NULL,
subscriber sysname  null,
subscriber_db sysname  null,
subscription_type int NULL,
subscriber_type tinyint NULL,
publisher_insertcount int NULL,
publisher_updatecount int NULL,
publisher_deletecount int NULL,
publisher_conflicts int NULL,
subscriber_insertcount int NULL,
subscriber_updatecount int NULL,
subscriber_deletecount int NULL,
subscriber_conflicts int NULL,
agent_type nvarchar(4000)
)

--新建临时表
  create table #qs_rep_merge
 (dbname sysname  not null
 ,name nvarchar(100)  not null
 ,status int NOT NULL
 ,publisher sysname  not null
 ,publisher_db sysname  not null
 ,publication sysname  null
 ,subscriber sysname  null
 ,subscriber_db sysname  null
 ,subscription_type int NULL
 ,start_time nvarchar(24)  null
 ,time nvarchar(24)  null
 ,duration int NULL
 ,comments nvarchar(255) NULL
 ,delivery_rate int NULL
 ,publisher_insertcount int NULL
 ,publisher_updatecount int NULL
 ,publisher_deletecount int NULL
 ,publisher_conflicts int NULL
 ,subscriber_insertcount int NULL
 ,subscriber_updatecount int NULL
 ,subscriber_deletecount int NULL
 ,subscriber_conflicts int NULL
 ,error_id int NULL
 ,job_id binary(16) NULL
 ,local_job bit NULL
 ,profile_id int NOT NULL
 ,agent_id int NOT NULL
 ,local_timestamp binary(8) NOT NULL
 ,offload_enabled bit NOT NULL
 ,offload_server sysname  null
 ,subscriber_type tinyint NULL
 )
 
 --新建临时表
    create table #qs_rep_distribution
 (dbname sysname  not null
 ,name nvarchar(100)  not null
 ,status int NOT NULL
 ,publisher sysname  not null
 ,publisher_db sysname  not null
 ,publication sysname  null
 ,subscriber sysname  null
 ,subscriber_db sysname  null
 ,subscription_type int NULL
 ,start_time nvarchar(24)  null
 ,time nvarchar(24)  null
 ,duration int NULL
 ,comments nvarchar(4000) NULL
 ,delivery_time int NULL
 ,delivered_transactions int NULL
 ,delivered_commands int NULL
 ,average_commands int NULL
 ,delivery_rate int NULL
 ,delivery_latency int NULL
 ,error_id INT NULL
 ,job_id binary(16) NULL
 ,local_job bit NULL
 ,profile_id int NOT NULL
 ,agent_id int NOT NULL
 ,local_timestamp binary(8) NOT NULL
 ,offload_enabled bit NOT NULL
 ,offload_server sysname  null
 ,subscriber_type tinyint NULL
 )
 
 --新建临时表
  create table #qs_rep_misc
 (
     name sysname  not null
 ,agent_type nvarchar(4000)
 ,status int NOT NULL
 ,message nvarchar(1024)
 ,start_time nvarchar(24)  null
    ,run_duration int
 ,job_id binary(16) NULL
 ,local_timestamp binary(8) NOT NULL
 )
 
 --新建临时表
    create table #qs_rep_snapshot
 (dbname sysname  not null
 ,name sysname  not null
 ,status int NOT NULL
 ,publisher sysname  not null
 ,publisher_db sysname  not null
 ,publication sysname  not null
 ,start_time nvarchar(24)  null
 ,time nvarchar(24)  null
 ,duration int NULL
 ,comments nvarchar(4000) NULL
 ,delivered_transactions int NULL
 ,delivered_commands int NULL
 ,delivery_rate float NULL
 ,error_id INT NULL
 ,job_id binary(16) NULL
 ,local_job bit NULL
 ,profile_id int NOT NULL
 ,agent_id int NOT NULL
 ,local_timestamp binary(8) NOT NULL
 ,dynamic_filter_login sysname NULL
 ,dynamic_filter_hostname sysname NULL)
 create table #qs_rep_logreader
 (dbname sysname  not null
 ,name sysname  not null
 ,status int NOT NULL
 ,publisher sysname  not null
 ,publisher_db sysname  not null
 ,start_time nvarchar(24)  null
 ,time nvarchar(24)  null
 ,duration int NULL
 ,comments nvarchar(4000) NULL
 ,delivery_time int NULL
 ,delivered_transactions int NULL
 ,delivered_commands int NULL
 ,average_commands int NULL
 ,delivery_rate int NULL
 ,delivery_latency int NULL
 ,error_id INT NULL
 ,job_id binary(16) NULL
 ,local_job bit NULL
 ,profile_id int NOT NULL
 ,agent_id int NOT NULL
 ,local_timestamp binary(8) NOT NULL
 )
 
 --新建临时表
 create table #qs_rep_qreader
 (dbname sysname  not null
 ,name sysname  not null
 ,status int NOT NULL
 ,start_time nvarchar(24)  null
 ,time nvarchar(24)  null
 ,duration int NULL
 ,comments nvarchar(4000) NULL
 ,transactions_processed int NULL
 ,commands_processed int NULL
 ,average_commands int NULL
 ,delivery_rate int NULL
 ,delivery_latency int NULL
 ,error_id INT NULL
 ,job_id binary(16) NULL
 ,profile_id int NULL
 ,agent_id int NOT NULL
 ,local_timestamp binary(8) NOT NULL
 )
 
 
    declare @db_name sysname
    declare @cmd nvarchar(1000)
 SET ANSI_WARNINGS OFF
 insert into #qs_rep_misc with (tablockx)
    select j.name, 'agent_type' = c.name,
            'status' = case isnull(jh.run_status, 5) -- mapped to never run
                when 0 then 5   -- Fail mapping
                when 1 then 2   -- Success mapping
                when 2 then 5   -- Retry mapping
                when 3 then 2   -- Shutdown mapping
                when 4 then 3   -- Inprogress mapping
                when 5 then 0   -- Unknown is mapped to never run
            end,
            jh.message, 'start_time' = convert(nvarchar(8), jh.run_date) + N' ' +
                                       stuff(stuff(right(convert(nvarchar(7), jh.run_time + 1000000), 6), 5, 0, N':'), 3, 0, N':') +
                                       + N'.000',
            jh.run_duration,
            'job_id' = convert(binary(16), j.job_id), 'local_timestamp' = 0 from
                msdb..sysjobs j
            LEFT OUTER JOIN msdb..sysjobhistory jh ON
            j.job_id = jh.job_id and
            jh.instance_id = (select max(instance_id) from msdb..sysjobhistory jh2 where
                jh2.job_id = j.job_id)
            INNER JOIN msdb..syscategories c ON
            j.category_id = c.category_id
            where j.category_id in ( 11, 12,16,17,18)
    order by j.job_id asc
 SET ANSI_WARNINGS ON
     
 declare hCdatabase CURSOR LOCAL FAST_FORWARD FOR
  select name from master.dbo.sysdatabases
   where
   category & 16 <> 0 and
   has_dbaccess(name) = 1
 for read only
 open hCdatabase
 fetch next from hCdatabase into @db_name
 while (@@fetch_status <> -1)
 begin
  
  -- 检查用户权限
  declare @has_pm bit
  select @cmd = quotename(@db_name) + '.dbo.sp_executesql'
  exec @cmd
   N'if is_member(N''db_owner'') = 1 or is_member(N''replmonitor'') = 1 set @has_pm = 1',
   N'@has_pm bit output',
   @has_pm output
  if @has_pm = 1
  begin
    -- 快照
    select @cmd = 'insert into #qs_rep_snapshot with (tablockx) ' +
      ' exec ' + quotename(@db_name) + '.dbo.sp_MSenum_snapshot @show_distdb = 1'
    exec (@cmd)
    -- 日志读取
    select @cmd = 'insert into #qs_rep_logreader with (tablockx) ' +
      ' exec ' + quotename(@db_name) + '.dbo.sp_MSenum_logreader @show_distdb = 1'
    exec (@cmd)
    -- 分发
    select @cmd = 'insert into #qs_rep_distribution with (tablockx) ' +
      ' exec ' + quotename(@db_name) + '.dbo.sp_MSenum_distribution @show_distdb = 1,  @exclude_anonymous = 0 '
    exec (@cmd)
    --merge
    select @cmd = 'insert into #qs_rep_merge with (tablockx) ' +
      ' exec ' + quotename(@db_name) + '.dbo.sp_MSenum_merge @show_distdb = 1,  @exclude_anonymous = 0 '
    exec (@cmd)
   
    select @cmd = 'insert into #qs_rep_qreader with (tablockx) ' +
      ' exec ' + quotename(@db_name) + '.dbo.sp_MSenum_qreader @show_distdb = 1'
    exec (@cmd)
   
  end
  fetch next from hCdatabase into @db_name
 end
 close hCdatabase
 deallocate hCdatabase
 declare  @misc_list_columns varchar(4000)
 declare  @snapshot_list_columns varchar(4000)
 declare  @logreader_list_columns varchar(4000)
 declare  @distribution_list_columns varchar(4000)
 declare  @merge_list_columns varchar(4000)
 declare  @qreader_list_columns varchar(4000)
 set @misc_list_columns = 'NULL dbname ' +
        ',a.name ' +
        ',a.status ' +
        ',NULL publisher ' +
        ',NULL publisher_db ' +
        ',NULL publication ' +
        ',convert(datetime,a.start_time,121) start_time ' +
        ',convert(datetime,a.start_time,121) [time] ' +
        ',a.run_duration run_duration ' +
        ',a.message comments ' +
        ',NULL delivered_transactions ' +
        ',NULL delivered_commands ' +
        ',NULL delivery_rate ' +
        ',convert(varchar(36), cast(a.job_id as uniqueidentifier)) job_id' +
        ',NULL delivery_latency ' +
        ',NULL subscriber ' +
        ',NULL subscriber_db ' +
        ',NULL subscription_type ' +
        ',NULL subscriber_type ' +
        ',NULL publisher_insertcount ' +
        ',NULL publisher_updatecount ' +
        ',NULL publisher_deletecount ' +
        ',NULL publisher_conflicts ' +
            ',NULL subscriber_insertcount ' +
        ',NULL subscriber_updatecount ' +
        ',NULL subscriber_deletecount ' +
        ',NULL subscriber_conflicts ' +
        ',5 agent_type '
 set @snapshot_list_columns =  'dbname '+
         ',name '+
         ',status '+
         ',publisher '+
         ',publisher_db '+
         ',publication '+
         ',convert(datetime,start_time,121) start_time '+
         ',convert(datetime,[time],121) [time] '+
         ',duration run_duration '+
         ',comments '+
         ',delivered_transactions '+
         ',delivered_commands '+
         ',delivery_rate '+
         ',convert(varchar(36), cast(job_id as uniqueidentifier)) job_id'+
         ',NULL delivery_latency '+
         ',NULL subscriber '+
         ',NULL subscriber_db '+
         ',NULL subscription_type '+
         ',NULL subscriber_type '+
         ',NULL publisher_insertcount '+
         ',NULL publisher_updatecount '+
         ',NULL publisher_deletecount '+
         ',NULL publisher_conflicts '+
             ',NULL subscriber_insertcount '+
         ',NULL subscriber_updatecount '+
         ',NULL subscriber_deletecount '+
         ',NULL subscriber_conflicts '+
         ',1 agent_type '
 set @logreader_list_columns = 'dbname ' +
         ',name ' +
         ',status ' +
         ',publisher ' +
         ',publisher_db ' +
         ',NULL publication ' +
         ',convert(datetime,start_time,121) start_time ' +
         ',convert(datetime,[time],121) [time] ' +
         ',duration run_duration ' +
         ',comments ' +
         ',delivered_transactions ' +
         ',delivered_commands ' +
         ',delivery_rate ' +
         ',convert(varchar(36), cast(job_id as uniqueidentifier)) job_id' +
         ',delivery_latency ' +
         ',NULL subscriber ' +
         ',NULL subscriber_db ' +
         ',NULL subscription_type ' +
         ',NULL subscriber_type ' +
         ',NULL publisher_insertcount ' +
         ',NULL publisher_updatecount ' +
         ',NULL publisher_deletecount ' +
         ',NULL publisher_conflicts ' +
             ',NULL subscriber_insertcount ' +
         ',NULL subscriber_updatecount ' +
         ',NULL subscriber_deletecount ' +
         ',NULL subscriber_conflicts ' +
         ',2 agent_type '
 set @distribution_list_columns = 'dbname '+
          ',name '+
          ',status '+
          ',publisher '+
          ',publisher_db '+
          ',publication '+
          ',convert(datetime,start_time,121) start_time '+
          ',convert(datetime,[time],121) [time] '+
          ',duration run_duration '+
          ',comments '+
          ',delivered_transactions '+
          ',delivered_commands '+
          ',delivery_rate '+
          ',convert(varchar(36), cast(job_id as uniqueidentifier)) job_id'+
          ',delivery_latency '+
          ',subscriber '+
          ',subscriber_db '+
          ',subscription_type '+
          ',NULL subscriber_type '+
          ',NULL publisher_insertcount '+
          ',NULL publisher_updatecount '+
          ',NULL publisher_deletecount '+
          ',NULL publisher_conflicts '+
              ',NULL subscriber_insertcount '+
          ',NULL subscriber_updatecount '+
          ',NULL subscriber_deletecount '+
          ',NULL subscriber_conflicts '+
          ',3 agent_type '
 set @merge_list_columns =  'dbname '+
         ',name '+
         ',status '+
         ',publisher '+
         ',publisher_db '+
         ',publication '+
         ',convert(datetime,start_time,121) start_time '+
         ',convert(datetime,[time],121) [time] '+
         ',duration run_duration '+
         ',comments '+
         ',NULL delivered_transactions '+
         ',NULL delivered_commands '+
         ',delivery_rate '+
         ',convert(varchar(36), cast(job_id as uniqueidentifier)) job_id'+
         ',NULL delivery_latency '+
         ',subscriber '+
         ',subscriber_db '+
         ',subscription_type '+
         ',NULL subscriber_type '+
         ',publisher_insertcount '+
         ',publisher_updatecount '+
         ',publisher_deletecount '+
         ',publisher_conflicts '+
             ',subscriber_insertcount '+
         ',subscriber_updatecount '+
         ',subscriber_deletecount '+
         ',subscriber_conflicts '+
         ',4 agent_type '
 set @qreader_list_columns =  'dbname '+
         ',name '+
         ',status '+
         ',NULL publisher '+
         ',NULL publisher_db '+
         ',NULL publication '+
         ',convert(datetime,start_time,121) start_time '+
         ',convert(datetime,[time],121) [time] '+
         ',duration run_duration '+
         ',comments '+
         ',NULL delivered_transactions '+
         ',NULL delivered_commands '+
         ',delivery_rate '+
         ',convert(varchar(36), cast(job_id as uniqueidentifier)) job_id'+
         ',delivery_latency '+
         ',NULL subscriber '+
         ',NULL subscriber_db '+
         ',NULL subscription_type '+
         ',NULL subscriber_type '+
         ',NULL publisher_insertcount '+
         ',NULL publisher_updatecount '+
         ',NULL publisher_deletecount '+
         ',NULL publisher_conflicts '+
             ',NULL subscriber_insertcount '+
         ',NULL subscriber_updatecount '+
         ',NULL subscriber_deletecount '+
         ',NULL subscriber_conflicts '+
         ',9 agent_type '
  
  set nocount off
  
  
  
  insert into #result
  exec(
  ' select ' +  @misc_list_columns +
  ' from #qs_rep_misc a with (tablockx) , msdb..sysjobs b ' +
  ' where a.job_id = b.job_id ' +
  ' and b.category_id in (10, 11, 12,16,17,18)' +
  ' union all ' +
  ' select ' + @snapshot_list_columns +
  ' from #qs_rep_snapshot with (tablockx) ' +
  ' union all ' +
  ' select ' + @logreader_list_columns +
  ' from #qs_rep_logreader with (tablockx) ' +
  ' union all ' +
  ' select ' + @distribution_list_columns +
  ' from #qs_rep_distribution with (tablockx) ' +
  ' union all ' +
  ' select ' + @merge_list_columns +
  ' from #qs_rep_merge with (tablockx) ' +
  ' union all ' +
  ' select ' + @qreader_list_columns +
  ' from #qs_rep_qreader with (tablockx) ')
  

 

select   dbname as [数据库名] ,name as [作业名],
case status
when 3 then '正在运行(复制)'
when 2 then '未运行'
when NULL then '未知'
end as [状态],
publisher as [发布服务器],
publisher_db as [发布数据库],
isnull(publication,'日志读取') as [发布名],
comments as [说明],subscriber as [订阅服务器],
subscriber_db as [订阅数据库] from #result
where publisher_db  is not null and agent_type=3
union all
select   dbname as [数据库名] ,name as [作业名],
case status
when 3 then '正在运行(日志读取)'
when 2 then '未运行'
when NULL then '未知'
end as [状态],
publisher as [发布服务器],
publisher_db as [发布数据库],
isnull(publication,'日志读取') as [发布名],
comments as [说明],subscriber as [订阅服务器],
subscriber_db as [订阅数据库] from #result
where publisher_db  is not null and agent_type=2

drop table #qs_rep_merge
drop table #qs_rep_distribution
drop table #qs_rep_misc
drop table #qs_rep_snapshot
drop table #qs_rep_logreader
drop table #qs_rep_qreader
drop table  #result
go

--**********************************************************************************************
--第二部分:监控复制进程是否运行、发布服务器到分发服务器、分发到订阅服务器传递的命令数

--**********************************************************************************************

/*

说明:根据自己环境需要定义下面变量:
set  @inactive=1  --默认不变
set  @virtual=-1  --默认不变
set @publisher='dbclu'  --发布服务器名
set @publisher_db='jin'  --发布数据库
set @publication='jinpolicy'  --发布名称
set @subscriber='jin81'  --订阅服务器名
set @subscriber_db='jin'  --订阅数据库
set @subscription_type=0  --默认不变

--------新建临时表(不同名)

*/

----2.1 监视第一台订阅服务器

use distribution
declare  @publisher sysname
declare @publisher_db sysname
declare @publication sysname
declare @subscriber sysname
declare @subscriber_db sysname
declare @subscription_type int
declare @retcode int 
declare @agent_id int 
declare @publisher_id int 
declare @subscriber_id int 
declare @lastrunts timestamp 
declare @avg_rate float 
declare @xact_seqno varbinary(16) 
declare @inactive int  
declare @virtual int


set  @inactive=1
set  @virtual=-1
set @publisher='dbclu'
set @publisher_db='jin'
set @publication='jinpolicy'
set @subscriber='jin81'
set @subscriber_db='jin'
set @subscription_type=0
set nocount on

--------新建临时表(不同名)
    create table #t001
    (
    subname sysname,
    waits int,
    est int
    )
   
   
if (@subscription_type not in (0,1)) 
    begin 
        raiserror(14200, 16, 3, '@subscription_type'
        return
    end 
 
    select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher) 
    if (@publisher_id is null) 
    begin 
        raiserror(21618, 16, -1, @publisher) 
        return  
    end 
    select @subscriber_id = server_id from sys.servers where upper(name) = upper(@subscriber) 
    if (@subscriber_id is null) 
    begin 
        raiserror(20032, 16, -1, @subscriber, @publisher) 
        return 
    end 
    -- 
    -- 获得agentID
    -- 
    select @agent_id = id 
    from 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 
    if (@agent_id is null) 
    begin 
        raiserror(14055, 16, -1) 
        return  
    end; 
    -- 
    -- 计算时间
    -- 
    with dist_sessions (start_time, runstatus, timestamp) 
    as 
    ( 
        select start_time, max(runstatus), max(timestamp)  
        from 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 dbo.MSpublications p join 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  
        end 
 
        select @lastrunts = max(timestamp) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 
 
    select @xact_seqno = xact_seqno 
            ,@avg_rate = delivery_rate 
    from dbo.MSdistribution_history 
    where agent_id = @agent_id 
        and timestamp = @lastrunts 

    select @xact_seqno = isnull(@xact_seqno, 0x0) 
            ,@avg_rate = isnull(@avg_rate, 0.0) 

    if (@avg_rate = 0.0) 
    begin 
        select @avg_rate = isnull(avg(delivery_rate),0.0) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 
 
--------新建临时表(不同名)
    create table #countab (pendingcmdcount int )
   
    insert into #countab (pendingcmdcount) 
        exec @retcode = 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
    -- 
    -- compute the time to process 
    -- return the resultset 
    -- 
--------新建临时表(不同名)
    create table #subname(subscriber char(30))
    insert into #subname (subscriber) values(@subscriber)
    
     insert into #t001
    select   subscriber as '订阅名',
        pendingcmdcount  as [等待的命令数]
        ,N'估计秒数' = case when (@avg_rate != 0.0)  
         then cast((cast(pendingcmdcount as float) / @avg_rate) as int) 
         else pendingcmdcount end 
    from #countab , #subname
 
--------删除临时表 
    drop table #countab
    drop table #subname

----2.2 监视第二台订阅服务器

set  @inactive=1
set  @virtual=-1
set @publisher='dbclu'
set @publisher_db='jin'
set @publication='dbtc'
set @subscriber='dbtc'
set @subscriber_db='policy'
set @subscription_type=0
set nocount on
--------新建临时表(不同名)
    create table #t002
    (
    subname sysname,
    waits int,
    est int
    )
if (@subscription_type not in (0,1)) 
    begin 
        raiserror(14200, 16, 3, '@subscription_type'
        return
    end 
 
    select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher) 
    if (@publisher_id is null) 
    begin 
        raiserror(21618, 16, -1, @publisher) 
        return  
    end 
    select @subscriber_id = server_id from sys.servers where upper(name) = upper(@subscriber) 
    if (@subscriber_id is null) 
    begin 
        raiserror(20032, 16, -1, @subscriber, @publisher) 
        return 
    end 

    select @agent_id = id 
    from 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 
    if (@agent_id is null) 
    begin 
        raiserror(14055, 16, -1) 
        return  
    end; 

    with dist_sessions (start_time, runstatus, timestamp) 
    as 
    ( 
        select start_time, max(runstatus), max(timestamp)  
        from 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 dbo.MSpublications p join 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  
        end 
 
        select @lastrunts = max(timestamp) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 

    select @xact_seqno = xact_seqno 
            ,@avg_rate = delivery_rate 
    from dbo.MSdistribution_history 
    where agent_id = @agent_id 
        and timestamp = @lastrunts 

    select @xact_seqno = isnull(@xact_seqno, 0x0) 
            ,@avg_rate = isnull(@avg_rate, 0.0) 

    if (@avg_rate = 0.0) 
    begin 
        select @avg_rate = isnull(avg(delivery_rate),0.0) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 

--------新建临时表(不同名)
    create table #countab1 (pendingcmdcount int )
    insert into #countab1 (pendingcmdcount) 
        exec @retcode = 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
    -- 
    -- compute the time to process 
    -- return the resultset 
    -- 
--------新建临时表(不同名)
    create table #subname1(subscriber char(30))
    insert into #subname1 (subscriber) values(@subscriber)
    
    insert into #t002
    select   subscriber as '订阅名',
        pendingcmdcount  as [等待的命令数]
        ,N'估计秒数' = case when (@avg_rate != 0.0)  
         then cast((cast(pendingcmdcount as float) / @avg_rate) as int) 
         else pendingcmdcount end 
    from #countab1 , #subname1
--------删除临时表
    drop table #countab1
    drop table #subname1
   
 
 ----2.3 监视第三台订阅服务器
set  @inactive=1
set  @virtual=-1
set @publisher='dbclu'
set @publisher_db='jin'
set @publication='rate168'
set @subscriber='dell'
set @subscriber_db='jin'
set @subscription_type=0
set nocount on

--------新建临时表(不同名)
    create table #t003
    (
    subname sysname,
    waits int,
    est int
    )
if (@subscription_type not in (0,1)) 
    begin 
        raiserror(14200, 16, 3, '@subscription_type'
        return
    end 
 
    select @publisher_id = server_id from sys.servers where upper(name) = upper(@publisher) 
    if (@publisher_id is null) 
    begin 
        raiserror(21618, 16, -1, @publisher) 
        return  
    end 
    select @subscriber_id = server_id from sys.servers where upper(name) = upper(@subscriber) 
    if (@subscriber_id is null) 
    begin 
        raiserror(20032, 16, -1, @subscriber, @publisher) 
        return 
    end 
 
    select @agent_id = id 
    from 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 
    if (@agent_id is null) 
    begin 
        raiserror(14055, 16, -1) 
        return  
    end; 
 
    with dist_sessions (start_time, runstatus, timestamp) 
    as 
    ( 
        select start_time, max(runstatus), max(timestamp)  
        from 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 dbo.MSpublications p join 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  
        end 
 
        select @lastrunts = max(timestamp) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 

    select @xact_seqno = xact_seqno 
            ,@avg_rate = delivery_rate 
    from dbo.MSdistribution_history 
    where agent_id = @agent_id 
        and timestamp = @lastrunts 
 
    select @xact_seqno = isnull(@xact_seqno, 0x0) 
            ,@avg_rate = isnull(@avg_rate, 0.0) 
 
    if (@avg_rate = 0.0) 
    begin 
        select @avg_rate = isnull(avg(delivery_rate),0.0) 
        from dbo.MSdistribution_history 
        where agent_id = @agent_id 
    end 

--------新建临时表(不同名)
    create table #countab2 (pendingcmdcount int )
   
    insert into #countab2 (pendingcmdcount) 
        exec @retcode = 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
 
--------新建临时表(不同名)
    create table #subname2(subscriber char(30))
    insert into #subname2 (subscriber) values(@subscriber)
    
     insert into #t003
    select   subscriber as '订阅名',
        pendingcmdcount  as [等待的命令数]
        ,N'估计秒数' = case when (@avg_rate != 0.0)  
         then cast((cast(pendingcmdcount as float) / @avg_rate) as int) 
         else pendingcmdcount end 
    from #countab2 , #subname2
   
--------删除临时表   
    drop table #countab2
    drop table #subname2
 
 
-----合并所有数据
    select c.subname as [订阅名] , c.waits as [等待的命令数] ,c.est as [估计秒数]  from
    (
    select * from #t001
 union all
   select * from #t002
   union all
   select * from #t003
   ) c
--------删除临时表   
  drop table #t001
  drop table #t002
  drop table #t003