This article is contributed. See the original author and article here.

Verbose log is heavily used  in replication troubleshooting. You need to find the right job to enable to verbose log. However, it’s not easy to find the  jobs when you have hundreds replication jobs in one server.

 

 

Here is how:

1.Distribution agent

Following queries list all the distribution agent jobs, including push subscriptions and pull subscriptions. (You may need add more clause to customize your queries). By default, the SQL Server agent job names equal to the agent names for push subscription, unless you explicitly modify the job names.

use distribution—in distributor server

if not exists(select 1 from sys.tables where name =’MSreplservers’)

begin

select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.name as publisherName ,ss.name as subscriber,a.subscriber_db, a.local_job From MSdistribution_agents a

inner join sys.servers sp on a.publisher_id=sp.server_id–publisher

inner join sys.servers ss on a.subscriber_id =ss.server_id–subscriber

left join msdb..sysjobs job on job.job_id=a.job_id

where a.subscription_type <>2— filter out the anonymous subscriber

end

else

begin

select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.srvname as publisherName ,ss.srvname as subscriber,a.subscriber_db, a.local_job From MSdistribution_agents a

inner join msreplservers sp on a.publisher_id=sp.srvid–publisher

inner join msreplservers ss on a.subscriber_id =ss.srvid–subscriber

left join msdb..sysjobs job on job.job_id=a.job_id

where a.subscription_type <>2— filter out the anonymous subscriber

end

 

Liwei_0-1595042676620.png

 

 

For push subscription, you can use the job name directly to find the job in distributor server.

Liwei_1-1595042676622.png

 

 

For  pull subscriptions(local_job=0), you need to run following query in the subscription database in subscriber server.

use subdb2        —in subscriber server

go

select job.name as JobName,distribution_agent as AgentName, *From MSreplication_subscriptions s inner join msdb.dbo.sysjobs job on s.agent_id=job.job_id

 

By default, the SQL Server agent job names equal to the agent names for pull subscription, unless you explicitly modify the job names.

Liwei_2-1595042676623.png

 

 

Liwei_3-1595042676625.png

 

 

 

 

 

2.Merge agent

Following queries list all the merge agent jobs, including push subscriptions and pull subscriptions. (You may need add more clause to customize your queries). By default, the SQL Server agent job names equal to the merge agent names.

use distribution—in distributor server

if not exists(select 1 from sys.tables where name =’MSreplservers’)

begin

select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.name as publisherName ,ss.name as subscriber,a.subscriber_db, a.local_job From MSmerge_agents a

inner join sys.servers sp on a.publisher_id=sp.server_id–publisher

inner join sys.servers ss on a.subscriber_id =ss.server_id–subscriber

left join msdb..sysjobs job on job.job_id=a.job_id

 

end

else

begin

select job.name JobName,a.name AgentName, a.publisher_db,a.publication as publicationName,sp.srvname as publisherName ,ss.srvname as subscriber,a.subscriber_db, a.local_job From MSmerge_agents a

inner join msreplservers sp on a.publisher_id=sp.srvid–publisher

inner join msreplservers ss on a.subscriber_id =ss.srvid–subscriber

left join msdb..sysjobs job on job.job_id=a.job_id

end

Liwei_4-1595042676626.png

 

 

For push subscription, you can use the job name directly to find the job in distributor server.

Liwei_5-1595042676627.png

 

 

 

For  pull subscriptions(local_job=0), you need to run following query in the subscription database in subscriber server.

use subdb6–in subscriber server

go

select job.name, sub.publisher,sub.publisher_db,sub.publication from  msdb..sysjobs job inner join msdb..sysjobsteps jobStep on job.job_id=jobStep.job_id

inner join MSsubscription_properties sub on sub.job_step_uid=jobStep.step_uid

 

Liwei_6-1595042676628.png

 

 

You can use the job name directly to find the job in subscriber server.

Liwei_7-1595042676630.png

 

 

 

 

 

3.Snapshot agent

Following queries list all the snapshot agent jobs. (You may need add more clause to customize your queries). By default, the SQL Server agent job names equal to the snapshot agent names, unless you explicitly modify the job names.

 

use distribution–in distributor server

if not exists(select 1 from sys.tables where name =’MSreplservers’)

begin

select job.name JobName, a.name AgentName , publisher_db,publication, s.data_source as publisher,

case publication_type

when 0 then ‘Transactional’

when 1 then ‘snapshot’

when 2 then ‘Merge’

end as publication_type

   From MSsnapshot_agents a inner join sys.servers s on a.publisher_id=s.server_id

   inner join msdb..sysjobs job on a.job_id=job.job_id

 

end

else

begin

select job.name JobName, a.name AgentName, publisher_db,publication, s.srvname as publisher,

case publication_type

when 0 then ‘Transactional’

when 1 then ‘snapshot’

when 2 then ‘Merge’

end as publication_type

   From MSsnapshot_agents a inner join MSreplservers s on a.publisher_id=s.srvid

   inner join msdb..sysjobs job on a.job_id=job.job_id

end

Liwei_8-1595042676631.png

 

You can use the job name directly to find the job in distributor server.

Liwei_9-1595042676632.png

 

 

4.Logreader agent

Following queries list all the log agent jobs, including push subscriptions and pull subscriptions. (You may need add more clause to customize your queries). By default, the SQL Server agent job names equal to the log reader agent names.

use distribution

if not exists(select 1 from sys.tables where name =’MSreplservers’)

begin

select job.name JobName, a.name AgentName, publisher_db,s.name as publisher

From MSlogreader_agents a inner join sys.servers s on a.publisher_id=s.server_id

Inner join msdb..sysjobs job on job.job_id=a.job_id

end

else

begin

select job.name JobName, a.name AgentName, publisher_db,s.srvname as publisher

From MSlogreader_agents a inner join MSreplservers s on a.publisher_id=s.srvid

Inner join msdb..sysjobs job on job.job_id=a.job_id

end

 

Please note, all publications of same publication database share same agent job

Please note, the agent name equals to job name by default, unless user modifies the job.

Liwei_10-1595042676633.png

 

 

 

Liwei_11-1595042676635.png

 

 

 

 

In some complex cases, you may need to review all the T-SQL statements issued by these agent jobs.

Here is how:

 

One agent have more than one connections to the servers, they have different session id but share the same process id. Once the process id is identified, you can use this process id to filter, using SQL Server profiler trace or xevent.

  1. Distribution agent

Distribution agent connects both distributor and subscriber. It has more than one connections in distributor. The application name of one connections equals to the agent name, that’s how I get all the connections of one specific agent.

 

1)For example, following query returns 6 agents.

use distribution

select  *  From MSdistribution_agents

Liwei_12-1595042676636.png

 

 

2)Let’s say I need to check the T-SQL of first agent.

If SQL Server trace files are collected in distributor and subscriber are collected, use the agent name to filter ApplicationName column to find the process id in distributor trace.

Then Use the process id to filter queries in distributor trace and subscriber trace.

 

If you need online troubleshoot,run following statement in distributor server.

select hostprocess as PID from sys.sysprocesses where program_name =’NODE1SQLAG-AdventureWorks-TranPubTest1-NODE3SQLAG-6′

 

Liwei_13-1595042676638.png

 

3)It returns the process id of this agent. Then you can use the process id of find all the sessions in both distributor server and subscriber server.

In distributor server

select @@servername,*From sys.sysprocesses where hostprocess=1832

Liwei_14-1595042676639.png

 

 

In subscriber server

select @@servername,program_name,*From sys.sysprocesses where hostprocess=1832

Liwei_15-1595042676640.png

 

 

4)An alternative is to run following query in subscription database, then use the hostprocess to filter…

use subdbName

select hostprocess, *From sys.sysprocesses where spid in

(

select spid From MSsubscription_agents

)

 

 

2.Merge agent

The behavior of Merge agent is as same as distribution agent.  It connects publisher, distributor and subscriber.

1.Run following query in distributor server.

use distribution

select * From MSmerge_agents

Liwei_16-1595042676641.png

 

 

2)Let’s say I need to check the T-SQL of first agent.

If SQL Server trace files are collected in publisher,distributor and subscriber are collected, please use the agent name to filter ApplicationName column to find the process id in publisher trace.

Then Use the process id to filter queries in publisher, distributor trace and subscriber trace.

 

3)If you need online troubleshoot,. run following statement in publisher server.

select @@servername,hostprocess From sys.sysprocesses where program_name in

(

‘NODE1SQLAG-AdventureWorks-MergePubTest-NODE3SQLAG-1’

)

Liwei_17-1595042676642.png

 

 

3)Use this process id to filter queries in distributor and subscriber, the same way I used for distribution agent.

 

 

3.Snapshot agent

The behavior of Snapshot agent is as same as distribution agent.  It connects publisher and distributor

1.Run following query in distributor server.

use distribution

select * From MSsnapshot_agents

Liwei_18-1595042676643.png

 

2)Let’s say I need to check the T-SQL of first agent.

If SQL Server trace files are collected in publisher and distributor are collected, please use the agent name to filter ApplicationName column to find the process id in publisher trace.

Then Use the process id to filter queries in publisher and distributor trace.

 

3)If you need online troubleshoot, run following statement in publisher server.

select @@servername,hostprocess From sys.sysprocesses where program_name in

(

‘NODE1SQLAG-AdventureWorks-MergePubTest-NODE3SQLAG-1’

)

 

3)Use this process id to filter queries in publisher and distributor , the same way I used for distribution agent.

 

4.Logreader agent

Logreader agent is little bit different. The agent name of MSlogreader_agents does not match anything. You need to use  following pattern to filter.

The application name of logreaer agent consists of Repl-LogReader-number-publicationDBName-number.

 

 

For example, if you need to collect process id of logreader of AdventureWorks, run following query in both publisher server and distributor server.

select program_name, hostprocess,@@servername From sys.sysprocesses where program_name like ‘Repl-LogReader%’ and program_name like ‘%AdventureWorks%’

Liwei_19-1595042676644.png

 

 

Liwei_20-1595042676645.png

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.