SQLServer复制出现the process could not execute 'sp
- WBOY原創
- 2016-06-07 15:39:331202瀏覽
事务复制涉及三3个Job(代理) 1、快照, 这个 只在要求初始化的情况下执行一次 2、日志读取, 读取配置复制之后的数据变化, 并生成复制命令, 这个一直在运行状态 3、分发代理, 这个负责把复制命令同步到订阅服务器, 这个也一直在运行状态。 4、除此之外，还有清
事务复制涉及三3个Job(代理)
1、快照, 这个 只在要求初始化的情况下执行一次
2、日志读取, 读取配置复制之后的数据变化, 并生成复制命令, 这个一直在运行状态
3、分发代理, 这个负责把复制命令同步到订阅服务器, 这个也一直在运行状态。
4、除此之外，还有清除过期的命令的作业（REPL-清除分发）和清除快照的作业（REPL-清除快照）。
--------------------------------------------------------------------------------------------------------------------
（1）事务命令过多导致日志读取器读取事务命令超时（或者服务器性能下降导致日志读取器读取事务命令超时）
--------------------------------------------------------------------------------------------------------------------
前几天，有一台服务器的复制出现问题，日志读取器的作业失败， 重试之后依然失败，在作业的历史记录中，报错消息只有以下错误信息：
the process could not execute 'sp_replcmds' on 'server name'
打开复制监视器之后，发现错误信息依然是：the process could not execute 'sp_replcmds' on 'server name' ，但是下面多了一行错误信息：
错误消息：
超时已过期(源：MSSQLServer,错误号:1003)
我们看下sp_replcmds 这个命令是干什么用的。
sp_replcmds：将在给定的数据库内运行 sp_replcmds 的第一个客户端视为日志读取器。返回被标记为复制的事务的命令。此存储过程在发布服务器的发布数据库上执行。
看来sp_replcmds 是取复制的命令的，结合上面的超时已过期的错误，可以想到该日志读取器在读取日志的时候超时了，即日志读取器的运行的查询的sql在运行时超时了。联想到最近我这台服务器有性能问题，因此查询消耗时间过长，也并非没有什么不可能的了。当然，也有可能是MSrepl_commands的命令比较多，可以在distribution库中运行以下代码查询数据量:
select count(1) from MSrepl_commands with(nolock) --目前我的数据库中有将近500w数据量，比较多，而其他的服务器就20w~30w的数据量。
确定原因之后，就需要把已经设置好的超时时间修改的长一点，以使日志读取器能在查询超时之前将标记为复制的事务的命令取出来。
1.启动复制监视器
2.右键-代理配置文件
3. 新建一个代理配置文件，将querytimeout的值修改为65533，这个是位置的最大值。
重启日志读取器的作业，作业就正常运行了。
对于这方面的一些资料，可以参考:
http://blogs.msdn.com/b/repltalk/archive/2010/07/13/using-verbose-history-agent-profile-while-troubleshooting-replication.aspx
http://www.tuicool.com/articles/IV3QRr
--------------------------------------------------------------------------------------------------------------------
（2）REPL-清除分发 作业运行时间过长导致锁表，造成日志读取器读取事务命令超时
--------------------------------------------------------------------------------------------------------------------
日志读取作业一直失败并报the process could not execute 'sp_replcmds' on 'server name' 错误。
在遇到这种情况的时候，尝试重启服务器的sql 代理，然后日志读取器可以正常工作一段时间（我遇到的情况是几个小时），但每天都会失败，并导致日志读取器作业失败。由于晚上也会失败，但由于订阅服务器需要及时取得数据，因此当时写了个作业每天在凌晨3点钟重启发布服务器的sql agent，这样才使得事务复制可以正常工作。
发布服务器是sql server 2000，重启sql server agent的脚本如下:
--开启xp_cmdshell
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
--重启sql server agent
--停止
use master
go
xp_cmdshell 'net stop SQLSERVERAGENT'
--启动
use master
go
xp_cmdshell 'net start SQLSERVERAGENT'
但这种方法只是暂时的方法，在度娘和谷歌之后，找到以下原因:
之前出现的发布服务器重启Agent才能够把数据复制到订阅服务器是因为以下原因：
1、第二步日志读取操作，会将发布服务器中的各种insert,update和delete操作的命令存放在MSrepl_commands表中。（这句话中的“第二步” 请查看本文章的开头）
2、清除过期的命令的作业（REPL-清除分发）是每隔十分钟运行一次，每次会自动清除MSrepl_commands表中的过期命令。
3、REPL-清除分发 作业是调用sp_MSdelete_publisherdb_trans存储过程来清除过期的命令，sp_MSdelete_publisherdb_trans这个存储过程是每次用游标只取2000条数据，然后删除。
现发现MSrepl_commands表已经有11106751条数据，因此，需要删除的数据量过大，导致 REPL-清除分发 作业一直在运行，从而一直在删除MSrepl_commands表的数据，造成MSrepl_commands表经常被锁住。
4、日志读取操作这个步骤需要将新的命令插入到表MSrepl_commands中，而此表因为删除操作被长时间锁住之后，无法插入新的命令。而在游标所取出来的2000条数据被删除之后,游标再次取2000条数据之前,这时候表MSrepl_commands没有被锁住, 日志读取操作 作业这时候可以将新的命令插入到表MSrepl_commands中。如果表MSrepl_commands被锁住的时间过长，则日志读取操作 作业将会超时失败，然后重试运行这个作业，直到重试十次之后，该作业失败。这时候就不会有新的数据复制到订阅服务器中的业务库中。
5、此时重启sql server agent，日志读取操作这个作业将会重启，然后重复上面的步骤，这时候就会 有新的数据复制到订阅服务器中的业务库中。
解决方法：
存储过程sp_MSdelete_publisherdb_trans 中的删除逻辑是每次取2000条数据，然后删除，一直循环直到删除完毕。
将这个存储过程每次取2000条数据修改为每次取100000条数据，在经过一天的删除操作之后，数据量又11106751变为三十多万。此时REPL-清除分发作业只需要几秒的时间就可以完成，不会再锁住表MSrepl_commands，其他作业可以顺利执行。
备注：
存储过程sp_MSdelete_publisherdb_trans中调用了存储过程sp_MSdelete_publisherdb_trans 来做的删除操作，因此修改的存储过程实际上是sp_MSdelete_publisherdb_trans。在表MSrepl_commands中的大部分数据被删除之后，我将参数100000修改回2000了。
微软亚太地区数据库技术支持组的一些博客也详细说了复制的一些问题，可以参考一下：
http://blog.itpub.net/25175503/viewspace-705405/ ----性能故障排除工具
http://blog.itpub.net/25175503/viewspace-705413/ ----日志读取器读者线程延迟
http://blog.itpub.net/25175503/viewspace-705826/ ---- 日志读取器写者线程延迟
http://blog.itpub.net/25175503/viewspace-705827/ ----分发代理读者线程延迟
http://blog.itpub.net/25175503/viewspace-706127/ ----分发代理写者线程延迟