事务复制清除的故障分析

  • Article

众所周知，创建分发数据库时，清除事务复制(Transactional Replication cleanup)会在分发服务器上，通过下列SQL Server 代理任务来清除不再需要的数据：

  1. 代理程序检查
  2. 代理程序历史记录清除
  3. 事务清除
  4. 分发清除
  5. 历史记录清除
  6. 过期订阅清除

分发清除代理程序

在所有订阅服务器都接收到事务后，分发清除代理程序(Distribution clean up: distribution)是通过SQL Agent作业来调度的，用以删除分发数据库中已提交的事务(已提交事务在分发数据库中保留一段定义好的时间，这段时间称为分发保持期)。分发清除代理程序在默认情况下每十分钟运行一次，它会执行存储过程sp_MSdistribution_cleanup。

下面是该清除过程自上而下的简单逻辑说明：

 sp_MSdistribution_cleanup 执行了
 sp_MSdistribution_delete 执行了
 sp_MSdelete_publisher_trans 执行了
 DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands...
 -和-
 sp_MSdelete_dodelete 执行了
 delete TOP(5000) MSrepl_transactions...

根据一些设置，比如分发保持期（Distribution Retention）、发布属性（immediate_sync）以及当前的订阅服务器水印，分发清除代理程序的清除步骤决定了哪些行可以从分发数据库的元数据表、msrepl_commands和msrepl_transactions中被删除。

常见问题的讨论

分发清除代理程序可能的常见问题症状：

  • 清除作业阻塞了分发代理或日志读取代理
  • 清除作业能运行但是不能从msrepl_commands、msrepl_transaction表中删除数据
  • 在msrepl_commands和msrepl_transactions上的任何操作的执行时间都很长，包括清除作业和分发代理程序中用于获取所有需要复制到订阅方命令的sp_MSget_repl_command存储过程。

故障分析过程

首先要做的是弄清现有的情况 - 我们需要对当前事务发布的配置做一些初始检查：

1.通过在分发服务器上执行下面的存储过程，确定分发服务器上配置的分发保持期(Distribution retention)：

 sp_helpdistributiondb
 go

检查min_distretention和max_distretention的值，确定分发服务器保存元数据的时间。

2.运行下面的语句，确定发布是否启用了immediate_sync属性。注意，每个发布都要检查到。

 use <publisherdb>
 go
 sp_helppublication
 go

检查immediate_sync的值 - 如果是开启的（值为1），那么被复制的数据修改和事务必须保持到保持期结束。

3.检查分发清除代理程序作业-“Distribution clean up: distribution”的历史记录，确保历史数据确实在被删除。

在分发服务器上，在SQL Server Management Studio中执行下面的语句，查看最近的分发清除作业历史记录：

 select * from msdb..sysjobhistory where message like '%Removed%'

显示结果应表示了在清除过程中有多少行数据被删除了。

4.监视分发清除代理程序作业 –  是什么操作在花费这么长的时间？如果问题是出现在清除的执行时间太长，则要找出是什么正在执行、要执行多久。清除作业在msrepl_transactions和msrepl_commands元数据表上进行删除操作。

 DELETE TOP(2000) MSrepl_commands WITH (PAGLOCK) from MSrepl_commands...
 delete TOP(5000) MSrepl_transactions...

那么，这些操作分别要花多久执行？为了得到答案和结论，我们可以在分发服务器上打开Profiler，并通过创建过滤器单独抓取清除作业的执行：

1.以ApplicationName创建一个过滤器，可以通过如下查询获得job id：

 select job_id from msdb..sysjobs where name like '%Distribution clean up%'

假设job id为5123859A-D04A-47D0-9483-F5C30C76BD76。在Profiler中创建ApplicationName过滤器。

2.在文件（File）菜单中，选择属性（Properties）

3.点击事件选择（Event Selection）标签

4.点击列过滤器（Column Filters）按钮

5.选择左面的ApplicationName，在右侧设置Like从句为%5123859A-D04A-47D0-9483-F5C30C76BD76%

6.点击确定（OK）接受默认的事件然后点击运行（Run）

通过分析抓取的SQL Trace日志，来获得分发清除代理程序作业的可能出现的问题所在。

可能的解决方案

基于以上的分析，如果遇到相关的分发清除代理程序的问题，我们可能采用如下的解决方法 – 当然，具体问题以及具体方法，需要视实际情况而定，不可一概而论：

1. 修改分发保持期的设置，比如为了减小元数据表(msrepl_commands和msrepl_transactions)的大小来提高相应的事务复制的性能，可以适当减小分发保持期的数值；

2. 修改发布属性immediate_sync的值，比如禁用这个属性，这样所有已经完成复制到订阅方的事务，不需要保持到分发保持期结束，就会从元数据表(msrepl_commands和msrepl_transactions)中清除，例如：

 sp_changepublication @publication='repltest', @property='immediate_sync', @value='FALSE'

更多关于immediate_sync 属性的描述，请参照联机丛书 - sp_changepublication.            

3. 定期重建分发数据库中的复制元数据表的统计信息及索引，特别是msrepl_commands和msrepl_transactions这两个元数据表的定期维护，来确保SQL能够保持对其现有数据及统计信息的时效性来产生，执行和完成比较有效率的操作。

4. 抓取Profiler日志和相应的sysprocesses查询结果，来检查分发清除代理程序是否与其他并发的进程操作有阻塞以及资源争用。如果有，排查并解决问题来使得分发清除代理程序的成功执行。