T-SQL Script: Return Replication Info, Plus Undistributed Commands

Posted in: Technical Track

In SQL Server Replication, a regular check is getting the number of undistributed commands for a transactional subscription. This can be done using the replication monitor (SQlmonitor.exe from the RUN prompt) or programmatically by running sp_replmonitorsubscriptionpendingcmds at the Distributor on the distribution database.

One issue with sp_replmonitorsubscriptionpendingcmds is that you will have to submit a few parameters without defaults (the required parameters are mentioned in the above link).

I wanted once to get status information about all subscriptions (Last logged message, status, agent, etc.), plus the number of undistributed commands for each subscription. For the replication info there is the MSdistribution_agents table, which is stored in the Distribution database and contains one row for each Distribution Agent. For the Agents’ history there is the MSdistribution_history table, which contains the history for Distribution agents, and is also stored in the Distribution database.

To return this, my idea was to get a list of publishers, publications, subscribers, and so on, into a temporary table, loop against the result, and run sp_replmonitorsubscriptionpendingcmds for each row, and then join against MSdistribution_history for the final result.

Here it is. All comments and additions are welcome.

Use Master;


IF object_id('tempdb..#tempsub2') is not null

    DROP TABLE #tempsub2

IF object_id('tempdb..#tempsub1') is not null

    DROP TABLE #tempsub1

Create TABLE #tempsub2 ( publisher SYSNAME , publisher_db SYSNAME, publication SYSNAME, subscriber SYSNAME, subscriber_db SYSNAME, Pending_Commands int , time_to_deliver_pending_Commands int)

DECLARE @publisher SYSNAME, @publisher_db SYSNAME,@publication SYSNAME,@subscriber SYSNAME,@subscriber_db SYSNAME

          ,CASE when sub1.anonymous_subid is not null then  upper(sub1.subscriber_name) ELSE UPPER (srv.name) END 'Subscriber'
 into #tempsub1
	SELECT * FROM distribution..msdistribution_agents agents
	Where subscriber_db not in ('virtual') -- Don't retrieve Virtual subscriptions
        and   anonymous_subid is null          -- Don't retrieve anonymous subscriptions
   ) sub1
   Inner join
	FROM distribution..MSreplication_monitordata
	WHERE publication_id is not null
        AND agent_type = 3  -- Distribution agent

	on  sub1.publisher_id = sub3.publisher_srvid
       and cast(sub1.job_id as uniqueidentifier) = sub3.job_id
	and sub1.publisher_db=sub3.publisher_db
	and sub1.publication= sub3.publication
	and sub1.subscription_type=sub3.publication_type
	and sub1.name =sub3.agent_name
	join master.sys.servers as srv
        on srv.server_id = sub1.subscriber_id

DECLARE subscribers cursor for SELECT publisher, publisher_db ,publication ,subscriber ,subscriber_db from #tempsub1

    OPEN subscribers FETCH NEXT FROM subscribers INTO @publisher, @publisher_db ,@publication ,@subscriber ,@subscriber_db


INSERT into #tempsub2

SELECT '''+ @publisher +''' , '''+ @publisher_db +''' ,'''+ @publication + ''' , ''' + @subscriber + ''' , ''' + @subscriber_db + ''' ,*
    FROM OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',''set fmtonly off EXEC distribution..sp_replmonitorsubscriptionpENDingcmds @publisher= '''''+ @publisher +''''' ,@subscription_type=0, @publisher_db= '''''+ @publisher_db +''''',@publication = '''''+ @publication+''''',@subscriber= '''''+@subscriber+''''' ,@subscriber_db='''''+@subscriber_db+''''''')

    FETCH NEXT FROM subscribers INTO @publisher,@publisher_db ,@publication ,@subscriber ,@subscriber_db


CLOSE subscribers DEALLOCATE subscribers

,comment.delivery_latency 'Delivery_latency MSs'
,comment.time 'Time of message'
,CASE comment.runstatus

when 1 then 'Started'

when 2 then 'Succeeded'

when 3 then 'In progress'

when 4 then 'Idle'

when 5 then 'Retrying'

when 6 then 'Failed ' END status ,

CASE  Info.subscription_type When 0 then 'Push' When 1 then 'Pull' When 2 then 'Anonymous' END 'Subscription Type'
,Info.name 'Distribution agent name'
,jobs.name 'Distribution_agent_job'


 #tempsub1 Info

inner join

#tempsub2 PENDing_commands

on Info.publisher_db = PENDing_commands.publisher_db
and Info.publication = PENDing_commands.publication
and Info.subscriber = PENDing_commands.subscriber
and Info.subscriber_db = PENDing_commands.subscriber_db

left outer join msdb..sysjobs jobs

on Info.job_id=jobs.job_id

inner join
SELECT time, agent_id ,runstatus,delivery_latency, comments,row_number() over ( partition by agent_id order by time desc ) as pos

 FROM distribution..MSdistribution_history

on comment.agent_id = Info.id

where comment.pos =1 ;

DROP TABLE #tempsub1 ;

Interested in working with Mohammed? Schedule a tech call.

11 Comments. Leave new

I made a change to your dynamic SQL so named instances would work.

SELECT ”’+ @publisher +”’ , ”’+ @publisher_db +”’ ,”’+ @publication + ”’ , ”’ + @subscriber + ”’ , ”’ + @subscriber_db + ”’ ,*
FROM OPENROWSET (”SQLOLEDB”,”Server=’+@@servername+’;TRUSTED_CONNECTION=YES;”,”set fmtonly off EXEC distribution..sp_replmonitorsubscriptionpENDingcmds @publisher= ””’+ @publisher +””’ ,@subscription_type=0, @publisher_db= ””’+ @publisher_db +””’,@publication = ””’+ @publication+””’,@subscriber= ””’+@subscriber+””’ ,@subscriber_db=””’+@subscriber_db+”””’)


Thanks eyechart for the add


Hi there, i’m just starting to dig into tsql for replication monitoring and found your script.

Its great, but i’m having an issue.

In the first subquery, the select from the msdistribution_agents table, in my case, with 17 records, all but 2 of them have ‘all’ as the publication field.

This causes the join to fail. I’m still digging into what ‘all’ means, but figured i’d report it here as well.

thx for the hard work!


Hi jeff ,

The first subquery (sub1) will get list of all agents in the replication subsystem.

“ALL” refers to logreader agents but later in the code I’m joining to another subquery (sub3) telling to retrieve only Distribution agents ( agent_type = 3 )

have you run the code as a whole block or you are running piece by piece ?



Your script is great for transactional replications
but what about merge replications
have any idea if or were can i find a similar script
many thanks!



Your query is great. I can get all the information. Is there anyway to get the actually pending command. Or can we update the quert to get the pending commands in another temp# table?


Mohammed Mawla
February 7, 2010 4:31 am


“sp_replmonitorsubscriptionpendingcmds” applies only for Transactional replication and the equivalent for Merge is “sp_showpendingchanges”

I think I will write a new blog for that

@SQLBrook :

To return the actual pending commands , you will need to run “sp_browsereplcmds” which returns the replicated commands stored in the distribution database


I don’t think it is a good idea to retrieve these commands or store them in a temp table because they can be thousands or millions and they will take so much time to retrieve ; better to retrieve them manually when there is a problem otherwise make sure DIST agent is running and delivering them


Using the MSdistribution_status view in distribution db might be a better option. You can join it with msarticles,mspublications etc for more info.

select a.article,d.name,ds.* from
MSdistribution_status ds inner join MSarticles a on ds.article_id=a.article_id
inner join MSdistribution_agents d on ds.agent_id=d.id


I have implemented this script however I don’t get any results. No rows return but when I run my script it gives me results.


Try this please.
select a.article,d.publisher_db,
d.subscriber_db, ds.* from
MSdistribution_status ds inner join MSarticles a on ds.article_id=a.article_id
inner join MSdistribution_agents d on ds.agent_id=d.id and a.publisher_db = d.publisher_db
Where d.subscriber_db not in (‘virtual’)
— Don’t retrieve Virtual subscriptions
and d.anonymous_subid is null — Don’t retrieve anonymous subscriptions

November 3, 2015 7:31 pm

Thanks , I have just been looking for info
about this subject for a while and yours is the best I have found out
so far. But, what concerning the conclusion? Are you positive concerning the source?


Leave a Reply

Your email address will not be published. Required fields are marked *