THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Find the distribution agent jobs

There may be times when you want to look up the job names for a group of distribution agents. For instance, you may want to be able to stop all the distribution agent jobs for a given publication. How do you look them up in a script?

A quick web search on finding the distribution agent job name comes back with suggestions similar to the following query:

SELECT a.publication,  
  FROM distribution.dbo.MSdistribution_agents a
       JOIN msdb.dbo.sysjobs j ON a.job_id = j.job_id

Unfortunately, this doesn’t quite work. Well, it works only if the distribution agents are running on the distributor, i.e. when the subscriptions are push. It does not work with any pull subscription.

The following is a demo script that works regardless whether the subscriptions are pull or push or a mix of both. This script is written to stop all the distribution agents on demand for a given publication named MY_PUBLICATION:

declare @publication sysname,

        @local_job int,

        @publisher_db sysname,

        @job_id uniqueidentifier,

        @job_name sysname,

        @subscriber sysname,

        @sql varchar(2000)


if object_id('tempdb..#sql') is not NULL

  drop table #sql


create table #sql (s varchar(2000))


declare cr cursor for

select d.publication, d.local_job, d.publisher_db, d.job_id,

  from distribution.dbo.MSdistribution_agents d

      join master.sys.servers s on d.subscriber_id = s.server_id

 where d.publication = 'MY_PUBLICATION'

order by d.publication,


open cr


fetch next from cr into @publication, @local_job, @publisher_db, @job_id, @subscriber


while @@fetch_status = 0


   if @local_job = 0

     select @sql = '

        select ''EXECUTE ' + @subscriber + '.msdb.dbo.sp_stop_job '''''' + + ''''''''

          from ' + @subscriber + '.' + @publisher_db + '.dbo.MSsubscription_properties sp

              join ' + @subscriber +
                   '.msdb.dbo.sysjobsteps js on sp.job_step_uid = js.step_uid

              join ' + @subscriber + '.msdb.dbo.sysjobs j on js.job_id = j.job_id

         where sp.publication = ''' + @publication +
        ''' and publisher = ''MY_PUBLISHER'' and publisher_db = ''' + @publisher_db + ''''


     select @sql = '

        select ''EXECUTE msdb.dbo.sp_stop_job '''''' + + ''''''''

          from distribution.dbo.MSdistribution_agents d join msdb.dbo.sysjobs j

              on d.job_id = j.job_id
              join master.sys.servers s on d.publisher_id = s.server_id

         where d.publication = ''' + @publication +
          ''' and = ''MY_PUBLISHER'' and d.publisher_db = ''' + @publisher_db + ''''


   --print @sql

   insert #sql

   EXECUTE (@sql)


   fetch next from cr into @publication, @local_job, @publisher_db, @job_id, @subscriber



close cr

deallocate cr


declare cr_sql cursor for

select s from #sql


open cr_sql


fetch next from cr_sql into @sql


while @@fetch_status = 0


      print @sql

      print ' '

      EXECUTE (@sql)

      fetch next from cr_sql into @sql


close cr_sql

deallocate cr_sql

The script assumes that the publisher is named MY_PUBLISHER and the distribution database is named distribution. In addition, the script must be run on the distributor. Obviously, you can modify the script to apply other search patterns (e.g. find all the distribution agent jobs for a published database) and/or apply other actions on the found jobs.

One quick note on the replication system table MSsubscription_properties. If you look up this system table in Books Online, you won’t find the column job_step_uid on the documented column list. But column is there. I don’t know if that is a documentation bug, or is left out on purpose. This table is the key to finding the distribution agent jobs of the pull subscriptions.

By the way, for a pull subscription, I have no idea what the value in the job_id column of the MSdistribution_agents table is for. If you know, I'd certainly appreciate your sharing the info.

Published Thursday, September 1, 2011 12:56 AM by Linchi Shea
Filed under:



Linchi Shea said:

Oops! There were some copy/paste errors in the script. I have made the correction.

September 1, 2011 8:04 PM

Linchi Shea said:

Hi Felon Job Finder;

Are you getting any specific error? You may need to change some of the hard-coded placeholder strings before you can use the script. The script works for me because I have two jobs running a version of the script regularly to stop and start some distribution agent jobs across multiple servers. We did this to help relieve the load on the distributor for that specifictime period and these distributon agents are of lower priority for the time period.

September 2, 2011 11:33 AM

Vasily_ka said:

Good. Thank you, but in my environment it is not work with where condition "and = ''MY_PUBLISHER'' ". Without your code didn't do it myself. Thanks again!

September 26, 2011 10:40 AM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement