脚本监控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
