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.

[sql collapse=”true”] Use Master;
GO

Set NOCOUNT ON;
GO

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

SELECT
sub3.publisher
,sub1.publisher_db
,sub1.publication
,CASE when sub1.anonymous_subid is not null then upper(sub1.subscriber_name) ELSE UPPER (srv.name) END ‘Subscriber’
,sub1.subscriber_db
,Sub1.job_id,sub1.id
,subscription_type
,sub1.name
into #tempsub1
FROM
(
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
(
SELECT
publisher
,publisher_db
,publication
,publication_type
,agent_name
,publisher_srvid
,job_id
FROM distribution..MSreplication_monitordata
WHERE publication_id is not null
AND agent_type = 3 — Distribution agent
)sub3

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

WHILE @@FETCH_STATUS = 0 BEGIN

INSERT into #tempsub2

EXEC
(

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

END

CLOSE subscribers DEALLOCATE subscribers

SELECT
Pending_commands.*
,comment.comments
,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’

FROM

#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
)comment

on comment.agent_id = Info.id

where comment.pos =1 ;
GO

DROP TABLE #tempsub1 ;
[/sql]

email

Interested in working with Mohammed? Schedule a tech call.

12 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+”””’)

Reply

Thanks eyechart for the add

Reply

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!

Reply

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 ?

HTH

Reply

Hi,
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!

Reply

Hello,

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?

Thanks,
-B

Reply
Mohammed Mawla
February 7, 2010 4:31 am

@commander:

“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

https://msdn.microsoft.com/en-us/library/ms176109.aspx

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

Reply

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

Reply

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.

Reply

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

Reply
marathistatus.in
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?

Reply

i ran the complete script it executed successfully but i dindt get any results..
can you please help

Reply

Leave a Reply

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