用这个存储过程查看复制的同步情况,默认参数快速查看是否同步: --在分发服务器的分发数据库上执行 if object_id('sp_repl_syn') is not null drop procedure sp_repl_syn GO create procedure sp_repl_syn @publication sysname='%' --模糊查询,发布名称 ,@subscriber sysname='%' --模糊查询,发布名称 ,@return_cmd_count bit=0 --是否返回未分发命令数,默认0不返回 as /********************** {VER EDITOR DATE COMMENT } {ver 1.0 summer.yang 2009-08-21 create procedure } {ver 1.1 summer.yang 2009-08-26 fix bug: result set has more than one same agent_id } {ver 1.2 summer.yang 2009-08-28 add: output data can be selected } {ver 1.3 summer.yang 2009-09-08 change: @return_cmd_count default=0 } REMARK: 1. 解析代码: sp_replmonitorsubscriptionpendingcmds 2. 解析代码: sys.sp_MSget_repl_commands 3. 不考虑可更新订阅的情况,可更新订阅需要修改代码 4. 不返回未分发命令数可以加快查询 5. undelivered_transactions或undelivered_commands=-1 说明有未分发的事务或命令 EXAMPLE: exec sp_repl_syn ************************/ set nocount on if object_id('tempdb.dbo.#agent') is not null drop table #agent if object_id('tempdb.dbo.#his') is not null drop table #his if object_id('tempdb.dbo.#max_xact') is not null drop table #max_xact if object_id('tempdb.dbo.#last_max_xact') is not null drop table #last_max_xact if object_id('tempdb.dbo.#repl') is not null drop table #repl if object_id('tempdb.dbo.##out') is not null drop table ##out --获得agent的基本信息 SELECT a.id as agent_id,a.publisher_database_id, a.publisher_db, a.publisher_id ,a.subscriber_id,a.subscriber_db, a.publication ,b.srvname as publisher,d.srvname as subscriber into #agent from MSdistribution_agents a with(nolock) inner join master.dbo.sysservers b with(nolock) on a.publisher_id =b.srvid inner join master.dbo.sysservers d with(nolock) on a.subscriber_id =d.srvid inner join MSpublications c with(nolock) on a.publisher_db=c.publisher_db and a.publication=c.publication where a.subscriber_id>0 /*yf.排除subscriber_db=virtual*/ and a.subscription_type = 0 and c.publication_type=0 and a.publication like @publication and d.srvname like @subscriber --and a.publisher_db like @publisher_db ----找出同步不正常的代理 ----1 = Start. 2 = Succeed. 3 = In progress. 4 = Idle. 5 = Retry. 6 = Fail. ----select '同步不正常' as runstatus,* from #agent_history where runstatus not in (2,3,4) --MSdistribution_history clustered index : agent_id, timestamp, runstatus, start_time, time ; 10000 rows ;with cte(agent_id,timestamp) as( select agent_id, max(timestamp) from dbo.MSdistribution_history group by agent_id ) select dh.agent_id, dh.timestamp, runstatus, start_time, time,xact_seqno as last_xact_seqno,delivery_rate as avg_rate ,duration,comments,current_delivery_rate,current_delivery_latency,delivered_transactions,delivered_commands ,average_commands as avg_commands,delivery_latency as avg_latency,total_delivered_commands,error_id into #his from dbo.MSdistribution_history dh join cte on dh.agent_id=cte.agent_id and dh.timestamp=cte.timestamp --注意不能被log_reader进程阻塞! with (READPAST) /*用msrepl_transactions替代MSrepl_commands,可以加快查询速度,提升约1秒*/ select publisher_database_id ,max(xact_seqno) as max_xact_seqno into #max_xact from msrepl_transactions mc with (READPAST) group by publisher_database_id select ah2.*,mx.max_xact_seqno into #last_max_xact from #agent a join #his ah2 on a.agent_id=ah2.agent_id join #max_xact mx on a.publisher_database_id=mx.publisher_database_id create table #repl(agent_id int,undelivered_transactions int,undelivered_commands int) if not exists(select * from #last_max_xact where last_xact_seqno<>max_xact_seqno) set @return_cmd_count=0 if @return_cmd_count=0 --fast select begin insert into #repl select l.agent_id, -1 as undelivered_transactions,-1 as undelivered_commands from #last_max_xact l join dbo.MSsubscriptions s WITH(NOLOCK) on s.agent_id = l.agent_id and l.last_xact_seqno<>l.max_xact_seqno --left join MSrepl_commands rc WITH (NOLOCK) -- ON (rc.article_id = s.article_id AND rc.publisher_database_id=s.publisher_database_id ) where s.status = 2 --订阅的状态 0 = 不活动 1 = 已订阅 2 = 活动 --and rc.xact_seqno > l.last_xact_seqno --and rc.xact_seqno <= l.max_xact_seqno group by l.agent_id end else begin -- Performance impact -- Issue the select for counts separately ??? insert into #repl select l.agent_id, count(distinct xact_seqno) as undelivered_transactions,count(*) as undelivered_commands from #last_max_xact l join dbo.MSsubscriptions s WITH(NOLOCK) on s.agent_id = l.agent_id and l.last_xact_seqno<>l.max_xact_seqno left join MSrepl_commands rc WITH (NOLOCK) ON (rc.article_id = s.article_id AND rc.publisher_database_id=s.publisher_database_id ) where s.status = 2 --订阅的状态 0 = 不活动 1 = 已订阅 2 = 活动 and rc.xact_seqno > l.last_xact_seqno and rc.xact_seqno <= l.max_xact_seqno group by l.agent_id -- end -- return the resultset select a.agent_id,a.publisher ,a.publisher_db ,a.publication ,a.subscriber ,a.subscriber_db ,case runstatus when 1 then 'Start' when 2 then 'Succeed' when 3 then 'In progress' when 4 then 'Idle' when 5 then 'Retry' when 6 then 'Fail' end as runstatus --,case when last_xact_seqno=max_xact_seqno then 0 else isnull(r.undelivered_transactions,0) end as undelivered_transactions --,case when last_xact_seqno=max_xact_seqno then 0 else isnull(r.undelivered_commands,0) end as undelivered_commands ,isnull(r.undelivered_transactions,0) as undlv_trans--undelivered_trans ,isnull(r.undelivered_commands,0) as undlv_cmds -- undelivered_cmds ,case when r.undelivered_commands=-1 then '' when (h.avg_rate<>0) then dbo.udf_Duration_IntToChar(cast(isnull((cast(r.undelivered_commands as float) / h.avg_rate),0) as int) ) else '0' end as prcs_time --EstimatedProcessTime-- compute the time to process ,comments,round(current_delivery_rate,0) as [cur_cmds/s],round(avg_rate,0) as [avg_cmds/s] ,round(convert(float,current_delivery_latency)/1000,0) as cur_latency , round(convert(float,avg_latency)/1000,0) as avg_latency--, avg_latency ,convert(char(20),h.start_time,120) start_time,dbo.udf_Duration_IntToChar(duration) as run_duration ,delivered_transactions as dlv_trans,delivered_commands as dlv_cmds ,avg_commands as [cmds/tran],total_delivered_commands as total_dlv_cmds ,error_id, convert(char(20),time,120) as update_time into ##out from #agent a left join #his h on a.agent_id=h.agent_id left join #repl r on a.agent_id=r.agent_id order by a.publication,a.subscriber select * from ##out print 'You can use the output data with: select * from ##out where comments not like ''%transaction%'' --失败的代理 select * from ##out where publication like ''%'' ' ------------------ --DECLARE @countab TABLE ( pendingcmdcount int ) --insert into @countab (pendingcmdcount) -- exec @retcode = sys.sp_MSget_repl_commands --解析这个sp -- @agent_id = @agent_id -- ,@last_xact_seqno = @xact_seqno -- ,@get_count = 2 -- ,@compatibility_level = 9000000 ------------------- GO grant execute on sp_repl_syn to public GO 可以使用以下SQL监控复制是否正常. exec sp_repl_syn --if exists(select * from MSreplication_monitordata where status>=5) if exists(select * from ##out where comments not like '%transaction%' ) begin raiserror 55555 @message end else begin print 'OK.' end