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

Mar 12, 2009 / By Mohammed Mawla

Tags:

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;
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 ;

10 Responses to “T-SQL Script: Return Replication Info, Plus Undistributed Commands”

  • eyechart says:

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

  • Mohammed Mawla says:

    Thanks eyechart for the add

  • jeff says:

    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 ?

    HTH

  • Commander says:

    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!

  • SQLBrook says:

    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

  • @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

    http://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

  • Akshay says:

    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

  • Raz says:

    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.

    • Sathiya says:

      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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>