Replication--查看未分发命令和预估所需时间
当复制有延迟时，我们可以使用复制监视器来查看各订阅的未分发命令书和预估所需时间，如下图：
但是当分发和订阅数比较多的时候，依次查看比较费时，我们可以使用sys.sp_replmonitorsubscriptionpendingcmds来查看，但是该命令需要输入多个参数，也比较累人，后从菠萝兄哪找寻得一个脚本，对该命令进行了一次封装：
--在分发服务器执行 USE distribution SELECT 'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N''' + a.publisher + ''', @publisher_db = N''' + a.publisher_db + ''', @publication = N''' + a.publication + ''', @subscriber = N''' + c.name + ''', @subscriber_db = N''' + b.subscriber_db + ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR) FROM dbo.MSreplication_monitordata a ( NOLOCK ) JOIN ( SELECT publication_id , subscriber_id , subscriber_db , subscription_type FROM MSsubscriptions (NOLOCK) GROUP BY publication_id , subscriber_id , subscriber_db , subscription_type ) b ON a.publication_id = b.publication_id JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id WHERE a.agent_type = 1
执行该脚本，可以生成相应命令，再依次执行命令可以获取我们想要的结果。
为方便查看，我在菠萝的脚本上做了改进，以便可以更方便查看：
--查看为传递到订阅的命令和预估时间 --在分发服务器执行 IF(OBJECT_ID('tempdb..#tmpSubscribers') IS NOT NULL) BEGIN DROP TABLE #tmpSubscribers END GO --IF(OBJECT_ID('tempdb..#tmpPendingResult') IS NOT NULL) --BEGIN --DROP TABLE #tmpPendingResult --END --GO --IF(OBJECT_ID('tempdb..#tmpSinglePendingResult') IS NOT NULL) --BEGIN --DROP TABLE #tmpSinglePendingResult --END GO USE distribution GO SELECT a.publisher ,a.publisher_db ,a.publication ,c.name as subscriber ,b.subscriber_db as subscriber_db ,CAST(b.subscription_type AS VARCHAR) as subscription_type ,'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N''' + a.publisher + ''', @publisher_db = N''' + a.publisher_db + ''', @publication = N''' + a.publication + ''', @subscriber = N''' + c.name + ''', @subscriber_db = N''' + b.subscriber_db + ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR) AS ScriptTxt INTO #tmpSubscribers FROM dbo.MSreplication_monitordata a ( NOLOCK ) JOIN ( SELECT publication_id , subscriber_id , subscriber_db , subscription_type FROM MSsubscriptions (NOLOCK) GROUP BY publication_id , subscriber_id , subscriber_db , subscription_type ) b ON a.publication_id = b.publication_id JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id WHERE a.agent_type = 1 --==================================================== --CREATE TABLE #tmpPendingResult --( --publisher NVARCHAR(200) --,publisher_db NVARCHAR(200) --,publication NVARCHAR(200) --,subscriber NVARCHAR(200) --,subscriber_db NVARCHAR(200) --,subscription_type NVARCHAR(200) --,pendingcmdcount BIGINT --,estimatedprocesstime BIGINT --) --CREATE TABLE #tmpSinglePendingResult --( --pendingcmdcount BIGINT --,estimatedprocesstime BIGINT --) --================================================== --使用游标遍历 DECLARE @publisher NVARCHAR(200);; DECLARE @publisher_db NVARCHAR(200); DECLARE @publication NVARCHAR(200); DECLARE @subscriber NVARCHAR(200);; DECLARE @subscriber_db NVARCHAR(200); DECLARE @subscription_type NVARCHAR(200); DECLARE @ScriptTxt NVARCHAR(MAX); DECLARE MyCursor CURSOR FOR SELECT publisher ,publisher_db ,publication ,subscriber ,subscriber_db ,subscription_type ,ScriptTxt FROM #tmpSubscribers; OPEN MyCursor FETCH NEXT FROM MyCursor INTO @publisher ,@publisher_db ,@publication ,@subscriber ,@subscriber_db ,@subscription_type ,@ScriptTxt; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @publisher AS publisher ,@publisher_db AS publisher_db ,@publication AS publication ,@subscriber AS subscriber ,@subscriber_db AS subscriber_db ,@subscription_type AS subscription_type ,@ScriptTxt; EXEC(@ScriptTxt) FETCH NEXT FROM MyCursor INTO @publisher ,@publisher_db ,@publication ,@subscriber ,@subscriber_db ,@subscription_type ,@ScriptTxt; END CLOSE MyCursor DEALLOCATE MyCursor
由于使用sp_replmonitorsubscriptionpendingcmds，无法将存储过程的结果插入到一个临时表中查看，因此想到参考其存储过程，编写一个类似脚本，于是有了下面脚本：
USE distribution go IF ( OBJECT_ID('dbo.sp_replmonitorsubscriptionpendingcmds_EX ') IS NOT NULL ) BEGIN DROP PROCEDURE dbo.sp_replmonitorsubscriptionpendingcmds_EX END GO CREATE PROCEDURE dbo.sp_replmonitorsubscriptionpendingcmds_EX AS BEGIN SET nocount ON CREATE TABLE #tmpPendingResult ( publisher NVARCHAR(200) , publisher_db NVARCHAR(200) , publication NVARCHAR(200) , subscriber NVARCHAR(200) , subscriber_db NVARCHAR(200) , subscription_type NVARCHAR(200) , pendingcmdcount BIGINT , estimatedprocesstime BIGINT ) --查找所有订阅 SELECT a.publisher , a.publisher_db , a.publication , c.name AS subscriber , b.subscriber_db AS subscriber_db , CAST(b.subscription_type AS VARCHAR) AS subscription_type INTO #tmpSubscribers FROM dbo.MSreplication_monitordata a ( NOLOCK ) JOIN ( SELECT publication_id , subscriber_id , subscriber_db , subscription_type FROM MSsubscriptions (NOLOCK) GROUP BY publication_id , subscriber_id , subscriber_db , subscription_type ) b ON a.publication_id = b.publication_id JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id WHERE a.agent_type = 1 DECLARE @count INT SELECT @count = COUNT(1) FROM #tmpSubscribers PRINT 'Subscriber Counter:' + CAST(@count AS VARCHAR(200)); DECLARE @publisher NVARCHAR(200);; DECLARE @publisher_db NVARCHAR(200); DECLARE @publication NVARCHAR(200); DECLARE @subscriber NVARCHAR(200);; DECLARE @subscriber_db NVARCHAR(200); DECLARE @subscription_type NVARCHAR(200); DECLARE MyCursor CURSOR FOR SELECT publisher , publisher_db , publication , subscriber , subscriber_db , subscription_type FROM #tmpSubscribers; OPEN MyCursor FETCH NEXT FROM MyCursor INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type; DECLARE @Error NVARCHAR(MAX) WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Error = '@publisher=' + @publisher + ';@publisher_db=' + @publisher_db + ';@publication=' + @publication + ';@subscriber=' + @subscriber + ';@subscriber_db' + @subscriber_db PRINT '开始:' + @Error; DECLARE @retcode INT , @agent_id INT , @publisher_id INT , @subscriber_id INT , @lastrunts TIMESTAMP , @avg_rate FLOAT , @xact_seqno VARBINARY(16) , @inactive INT = 1 , @virtual INT = -1 -- -- PAL security check done inside sp_MSget_repl_commands -- security: Has to be executed from distribution database -- -- if sys.fn_MSrepl_isdistdb (db_name()) != 1 -- begin -- --raiserror (21482, 16, -1, 'sp_replmonitorsubscriptionpendingcmds', 'distribution') -- --return 1 --SELECT @Error='@publisher='+@publisher+';@publisher_db='+@publisher_db --+';@publication='+@publication+';@subscriber='+@subscriber+';@subscriber_db'+@subscriber_db --PRINT @Error --CONTINUE; -- end -- -- validate @subscription_type -- IF ( @subscription_type NOT IN ( 0, 1 ) ) BEGIN --raiserror(14200, 16, 3, '@subscription_type') --return 1 PRINT 'ERROR IN subscription_type' CONTINUE; END -- -- get the server ids for publisher and subscriber -- 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 1 PRINT 'ERROR IN publisher_id' CONTINUE; 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 1 PRINT 'ERROR IN subscriber_id' CONTINUE; END -- -- get the agent id -- 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 (1) PRINT 'ERROR IN agent_id' CONTINUE; END; -- -- Compute timestamp for latest run -- WITH dist_sessions ( start_time, runstatus, timestamp ) AS ( SELECT start_time , MAX(runstatus) , MAX(timestamp) FROM dbo.MSdistribution_history WHERE agent_id = @agent_id AND runstatus IN ( 2, 3, 4 ) GROUP BY start_time ) SELECT @lastrunts = MAX(timestamp) FROM dist_sessions; IF ( @lastrunts IS NULL ) BEGIN -- -- Distribution agent has not run successfully even once -- and virtual subscription of immediate sync publication is inactive (snapshot has not run), no point of returning any counts -- see SQLBU#320752, orig fix SD#881433, and regression bug VSTS# 140179 before you attempt to fix it differently :) 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 0 INSERT INTO #tmpPendingResult ( publisher , publisher_db , publication , subscriber , subscriber_db , subscription_type , pendingcmdcount , estimatedprocesstime ) SELECT @publisher , @publisher_db , @publication , @subscriber , @subscriber_db , @subscription_type , 0 , 0 END -- -- Grab the max timestamp -- SELECT @lastrunts = MAX(timestamp) FROM dbo.MSdistribution_history WHERE agent_id = @agent_id END -- -- get delivery rate for the latest completed run -- get the latest sequence number -- SELECT @xact_seqno = xact_seqno , @avg_rate = delivery_rate FROM dbo.MSdistribution_history WHERE agent_id = @agent_id AND timestamp = @lastrunts -- -- if no rows are selected in last query -- explicitly initialize these variables -- SELECT @xact_seqno = ISNULL(@xact_seqno, 0x0) , @avg_rate = ISNULL(@avg_rate, 0.0) -- -- if we do not have completed run -- get the average for the agent in all runs -- 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 -- -- get the count of undelivered commands -- PAL check done inside -- DECLARE @countab TABLE ( 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 1 CONTINUE; -- -- compute the time to process -- return the resultset -- INSERT INTO #tmpPendingResult ( publisher , publisher_db , publication , subscriber , subscriber_db , subscription_type , pendingcmdcount , estimatedprocesstime ) SELECT @publisher , @publisher_db , @publication , @subscriber , @subscriber_db , @subscription_type , pendingcmdcount , CASE WHEN ( @avg_rate != 0.0 ) THEN CAST(( CAST(pendingcmdcount AS FLOAT) / @avg_rate ) AS INT) ELSE pendingcmdcount END FROM @countab -- -- all done -- --CONTINUE; FETCH NEXT FROM MyCursor INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type; END CLOSE MyCursor DEALLOCATE MyCursor SELECT * FROM #tmpPendingResult END GO --========================================================= --测试 EXEC dbo.sp_replmonitorsubscriptionpendingcmds_EX
上面相对使用起来更方便些。哈哈
--============================================================================================
来个妹子给大家降降温
