sqlsever Java监控_脚本监控sqlserver 2008 R2 replication性能

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

/*

说明:命令在主用用于监控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