脚本在分发服务器上执行!我主要使用来监控复制情况.下面是个案例，仅供参考！根据实际情况修改相关参数
/*
说明:命令在主用用于监控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
,comments nvarchar(4000) NulL
,delivery_time int NulL
,delivered_transactions int NulL
,delivered_commands int NulL
,average_commands int NulL
,delivery_latency int NulL
,error_ID INT NulL
,subscriber_type tinyint NulL
)
--新建临时表
create table #qs_rep_misc
(
name sysname not null
,agent_type nvarchar(4000)
,message nvarchar(1024)
,start_time nvarchar(24) null
,run_duration int
,local_timestamp binary(8) NOT NulL
)
--新建临时表
create table #qs_rep_snapshot
(dbname sysname not null
,name sysname not null
,publication sysname not null
,delivery_rate float NulL
,dynamic_filter_login sysname NulL
,dynamic_filter_hostname sysname NulL)
create table #qs_rep_logreader
(dbname sysname not null
,local_timestamp binary(8) NOT NulL
)
--新建临时表
create table #qs_rep_qreader
(dbname sysname not null
,transactions_processed int NulL
,commands_processed int NulL
,profile_ID int 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,N':'),3,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 ' +
',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 '+
',start_time,121) start_time '+
',[time],121) [time] '+
',duration run_duration '+
',comments '+
',delivered_transactions '+
',delivered_commands '+
',delivery_rate '+
',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 ' +
',121) start_time ' +
',121) [time] ' +
',duration run_duration ' +
',comments ' +
',delivered_transactions ' +
',delivered_commands ' +
',delivery_rate ' +
',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 '+
',121) start_time '+
',121) [time] '+
',duration run_duration '+
',comments '+
',delivered_transactions '+
',delivered_commands '+
',delivery_rate '+
',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 '+
',NulL delivered_transactions '+
',NulL delivered_commands '+
',subscriber '+
',subscriber_db '+
',subscription_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 '+
',NulL publisher '+
',NulL publisher_db '+
',NulL publication '+
',delivery_latency '+
',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,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 [数据库名],
case status
when 3 then '正在运行(日志读取)'
when 2 then '未运行'
when NulL then '未知'
end 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,'@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,-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,@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,-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,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,
est int
)
if (@subscription_type not in (0,@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,-1)
return
end;
with dist_sessions (start_time,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
,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
,@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 '订阅名',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,@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,-1)
return
end;
with dist_sessions (start_time,@avg_rate = delivery_rate
from dbo.MSdistribution_history
where agent_ID = @agent_ID
and timestamp = @lastrunts
select @xact_seqno = isnull(@xact_seqno,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
总结以上是内存溢出为你收集整理的脚本监控sqlserver 2008 R2 replication性能全部内容，希望文章能够帮你解决脚本监控sqlserver 2008 R2 replication性能所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）