找到replication相关的作业和sql 语句

在排查replication问题过程中，经常需要为指定的agent添加verbose
log。这首先要找到相关的作业。但在复杂的replication环境中，一台服务器里包可能含了上百个作业，一个个去点开定义去查看简直就是噩梦。下面的文章介绍了如何快速定位作业。

所有信息都可以在Distributor 服务器里找到，以Merge replication为例子：我们发现某个订阅的上传速度很慢，接下来需要找到相应的merge agent（后面的段落中除非特别声明，否则所有的操作都是在distributor服务器的distribution数据库内操作）

 

1. sys.servers表，每当添加了一个publisher或subscriber之后，相应的记录都会存储在sys.servers表中，
2. Msdb数据库的sysjobs表包含了所有的作业信息
3. MSmerge_agents表，存储了publisher_db,publication,
        subscriber_sv,subscriber_name这些信息.

联合3张表就可以找到相应的merge agent job

select isnull(jobs.name,a.name) as name, a.publisher_db,a.publication as publicationName,a.subscriber_db,a.subscriber_name,s.data_source as publisherName,a.local_job From MSmerge_agents a inner join sys.servers s on a.publisher_id=s.server_id

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

 

如果是一个push模式的订阅（local_job=1），直接使用name去匹配sql server agent的作业即可（实际上msmerge_agents的name默认情况和jobs的name相同，但有可能用户意外地修改了作业名称）。

如果是一个pull模式的订阅（local_job=0），就需要去subscriber查找了。pull模式的merge agent job的命名规律:publisher-publisherDB-publicationName-subscriber-subscriberDB-serialnumber

根据这个规律，就可以找到相应的作业了。如果sub端的作业也被改名了，可以通过查询msdb的sysjobsteps找到相应的command，然后去匹配即可 （command就是replmerg.exe后面的参数，也就是在merge agent作业里的定义）

select j.name ,js.command ,js.subsystem from  msdb..sysjobs j inner join msdb..sysjobsteps js on j.job_id=js.job_id

where subsystem='Merge'

Snapshot agent

select  jobs.name, 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 jobs on a.job_id=jobs.job_id

jobs.name就是sql serveragent job的名称

命名规则：publisher-publisherDB-publicationName -serial number
 

Logreader agent

select  jobs.name, publisher_db,s.data_source as publisher

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

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

jobs.name就是sql server agent job的名称

同一个数据库的多个publication共用一个Logreaderagent）

Distribution agent

select isnull(jobs.name,a.name) as name, a.publisher_db,a.publication as publicationName,s.name as publisherName ,s.data_source as publisherName,s1.data_source as subscriber,a.subscriber_db, a.local_job From MSdistribution_agents a
inner join sys.servers s on a.publisher_id=s.server_id

inner join
sys.servers s1 on a.subscriber_id =s1.server_id

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

where a.subscription_type <>2--- filter out theanonymous subscriber

如果是一个push模式的订阅（local_job=1），直接用name匹配即可。

如果是一个pull模式的订阅（local_job=0），就比较麻烦了，因为作业名称没有什么规律可言。下面是一个subscriber端job的截图

我们需要查询subscriber的subscription database.其中的distribution
agent列直接匹配作业的名称（如果作业名称被修改，则需要查询sysjobs）。

select job.name ,s.distribution_agent from  msdb..sysjobs job inner join MSreplication_subscriptions
s on job.job_id=s.agent_id

如何找到replication运行时执行的语句

有时候在解决复杂的replication问题时，我们需要捕获sql server trace，查找其中的原因。但在实际的生产环境中，sqlserver会同时处理大量的语句，大量的非相关的sql语句会干扰我们排查问题。假设一台订阅器上有多个mergeagent在同时运行，那我们如何从trace里找出我们需要的信息呢？

1.         抓取publisher, distributor 和 subscriber三台服务器的trace

2.         在distribution数据库里查询(不区分pull还是push)：

a)  selectname,*FromMSmerge_agents

3.         在这里得到了merge agent的名称，用ApplicationName去过滤publisher的端收集到的trace，得到clientProcessID，用这个clientProcessID就可以得到merge agent发出的所有语句了。再用这个clientProcessID，可以找到在distributor和subscriber端发出的语句。（每个merge agent都会有单独的ProcessID，所有用这个processID就可以找到同一个merge agent在publisher, distributor和subscriber发出的语句了）

处理distribution agent也很类似，只需要将第二步的MSmerge_agents替换成MSdistribution_agents即可。用name去过滤distributor的trace,得到clientProcessid,根据这个clientProcessid，到sub进行过滤。

Logreaderagent的applicationName的命名规则就比较特殊了，为Repl-LogReader-number-publicationDBName-number，用这个去匹配publisher和distributor上的trace内容即可。

[2014-08-10 更新]

Snapshot agent的只会在publisher生成语句，首先通过snapshot agent name，用这个名称去过滤，然后找到process id即可