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

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]