执行大事务导致订阅数据库延迟的处理方法

在发布服务器上执行一个大事务，比如全表更新，用于数据初始化时，当传达到订阅服务器时，会分解成许多条命令，或者该命令会改写原sql，造成效率降低。

先说明下原理：

先查看订阅服务器正在执行的事务号（订阅服务器的MSreplication_subscriptions表），再确认分发数据库中下发的事务和命令是否准确（订阅服务器的MSrepl_transactions和MSrepl_commands表），最后用sp_setsubscriptionxactseqno函数对事务进行跳过

sp_setsubscriptionxactseqno [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication'
        , [ @xact_seqno = ] xact_seqno       

以实例说明：

复制结构：

发布服务器ETCP-PC，发布名称local_repl，发布数据库newperf，订阅服务器也是ETCP-PC（多实例），订阅数据库newperf

执行一个大事务，对orders1全表更新：

查看复制监视器，在传递事务过程中非常的慢，所以停止订阅

查询订阅服务器正在执行的事务号：0x000005CC000001780033

SELECT  transaction_timestamp
FROM    MSreplication_subscriptions
WHERE   publisher = 'ETCP-PC'
        AND publisher_db = 'newperf'
        AND publication = 'local_repl'

查询日至服务器比对一下，可以看出当前事务被拆分成999997条命令，也就是说每一行都是一条更改的命令。

确认无误后，在订阅数据库上执行跳过，注意事务号指定当前事务的后续第一个事务0x000005CC000001950004 ，上个查询中查找出来的

EXEC sp_setsubscriptionxactseqno @publisher =  'ETCP-PC'
        , @publisher_db =  'newperf'
        , @publication =  'local_repl'
        , @xact_seqno =  0x000005CC000001950004

在订阅服务器上确认，数据没有改变，但后续的事务已经执行：

等一会延迟就变低了，后续可以再从库上进行主库数据的一致性更新，直到主从数据一致为止。

上述只是阐述了如何补救，其实真正的安全是做到预防，如果有主库大事务的程序或操作，建议将大事务分解成若干小事务，并分时段进行提交。

相关连接：SQL Server复制系列4 - Transactional replication中如何跳过一个事务

       sp_setsubscriptionxactseqno函数

       在SQL Server 2005/2008事务复制中如何跳过一个事务