Replication-view undistributed commands and estimated time required

When there is a replication delay, we can use the replication monitor to view the undistributed commands and estimated time required for each subscription, such:

You can use the following script to implement it:

-- View the commands and estimated time passed to the subscription -- execute if (object_id ('tempdb .. # tmpsubscribers ') is not null) begindrop table # tmpsubscribersendgo -- 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 -- endgouse distribution goselect. publisher,. publisher_db,. 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''' +. publisher + ''', @ publisher_db = n''' +. publisher_db + ''', @ publication = n''' +. publication + ''', @ subscriber = n''' + C. name + ''', @ subscriber_db = n''' + B. subscriber_db + ''', @ subscription_type = '+ Cast (B. subscription_type as varchar) as scripttxtinto # tmpsubscribersfrom DBO. revoke 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. publication_id = B. publication_id join sys. servers C (nolock) on B. subscriber_id = C. server_idwhere. 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 )--, pendingtransfercount bigint --, estimatedprocesstime bigint --) -- Create Table # tmpsinglependingresult -- (-- pendingtransfercount bigint --, estimatedprocesstime bigint --) -- ===================================================== ===========-- use a cursor to traverse declare @ publisher nvarchar (200 );; declare @ publisher_db nvarchar (200); declare @ publication nvarchar (200); declare @ subscriber nvarchar (200); declare @ subscriber_db nvarchar (200 ); declare @ brief nvarchar (200); declare @ scripttxt nvarchar (max); declare mycursor cursor forselect publisher, publisher_db, publication, subscriber, comment, subscription_type, comment # tmpsubscribers; open publish next from mycursor into @ publisher, @ publisher_db, @ publication, @ subscriber, @ subscriber_db, @ subscription_type, @ scripttxt; while @ fetch_status = 0 beginselect @ 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; endclose mycursordeallocate mycursor

 

Replication-view undistributed commands and estimated time required