Replication--查看未分发命令和预估所需时间

当复制有延迟时，我们可以使用复制监视器来查看各订阅的未分发命令书和预估所需时间，如下图：

用以下的脚本可以帮我们来实现：

--查看为传递到订阅的命令和预估时间
--在分发服务器执行 
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