Howto find the Name of Logreader and Distribution Task

[Thomas Hase]

Hi NG,

MSSQL2k8 Transactional Replication

How can I find with T-SQL for a known publication
the name or ID of the related task for logreader-task, snapshot-task
and distribution task, if the task was manually renamed.

Thanks in advanced.

Thomas

[Paul Ibison]

Hi Thomas,

here are the queries you need:

distribution agents (assuming push):

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSdistribution_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

log reader agents:

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

snapshot agents:

select a.job_id, a.name as OldName, b.name as CurrentName
from distribution.dbo.MSsnapshot_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

HTH,

Paul Ibison

[Thomas Hase]

Hallo Paul,

thanks it works, but my question was wrong.

On the MSSQL are many (10) publications.

Pub01
Pub02
..
Pub10

An I want to find VIA the name of publication all
related jobs.

exec sp_show_me_all_related_jobs 'pubName'

I am looking for a way to find out

Thomas

[Paul Ibison]

You can adapt my queries using a union statement as follows:

select a.* from
(
select a.publication, b.name as CurrentJobName

from distribution.dbo.MSdistribution_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

union
select a.publication, b.name as CurrentJobName

from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

union
select a.publication, b.name as CurrentJobName

from distribution.dbo.MSsnapshot_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

) a
where a.publication = 'yourpubname'

This works for transactional. If you need merge then let me know.

HTH,

Pual Ibison

[Thomas Hase]

Hallo Paul,

thanks, I haven't seen the collumn "publication".

If I try:

select A.PUBLICATION, a.job_id, a.name as OldName, b.name as
CurrentName

from distribution.dbo.MSlogreader_agents a
inner join msdb.dbo.sysjobs b
on a.job_id = b.job_id

then I get in column: publication -> "ALL" instead the name of the
publication

ALL 0x8E585BD4349ABC45B8DB66C8A540974A ST-BB-SRV2-DB1-7 ..
ALL 0x9EF19737EBB26D45BAB1A3013CF3D09F ST-BB-SRV2-DB2-6 ..

Is this a missconfig of my replication?

Thomas

[Paul Ibison]

Only applies to the log reader agent - they don't belong to a particular
publication - just a transactionally replicated database.
The other ones will give the publication name.
HTH,
Paul Ibison

[Thomas Hase]

Hallo Paul,

thank you

Thomas