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, j.name
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, s.name
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, s.name
open cr
fetch next from cr into @publication, @local_job, @publisher_db, @job_id, @subscriber
while @@fetch_status = 0
begin
if @local_job = 0
select @sql = '
select ''EXECUTE ' + @subscriber + '.msdb.dbo.sp_stop_job '''''' + j.name + ''''''''
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 + ''''
else
select @sql = '
select ''EXECUTE msdb.dbo.sp_stop_job '''''' + j.name + ''''''''
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 s.name = ''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
end
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
begin
print @sql
print ' '
EXECUTE (@sql)
fetch next from cr_sql into @sql
end
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.