How to find SQL Server Replication related jobs and T-SQL statements

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

 

 

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

 

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.

 

 

 

 

 

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

 

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

 

 

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

 

 

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

 

 

 

 

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

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

 

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.

 

 

 

 

 

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

 

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 ='NODE1\SQLAG-AdventureWorks-TranPubTest1-NODE3\SQLAG-6'

 

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

 

In subscriber server

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

 

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

 

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
(
'NODE1\SQLAG-AdventureWorks-MergePubTest-NODE3\SQLAG-1'
)

 

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

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
(
'NODE1\SQLAG-AdventureWorks-MergePubTest-NODE3\SQLAG-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%'