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.

The Challenge with sp_replmonitorsubscriptionpendingcmds

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.

Automating Pending Command Checks

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.

SQL Solution Script

Here it is. All comments and additions are welcome.

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 ;