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