Replication--复制延迟的诊断和解决

--====================================================================
--检查当前服务上那些数据库搭建了发布
SELECT name,is_published 
FROM sys.databases WITH(NOLOCK)
WHERE is_published = 1
AND state = 0

--====================================================================
--检查发布设置,在发布的数据库上运行
SELECT
name AS ReplicationName,
[immediate_sync],
[allow_anonymous]
FROM [dbo].[syspublications]
--====================================================================
--将所有事务发布设置为immediate_sync=0，allow_anonymous=1时，
--复制生成的命令一旦发送给所有订阅服务器后，立即删除，从而减少分发库保存的命令数。
exec sp_changepublication 
@publication = '发布名称' ,
@property = 'allow_anonymous' , 
@value = false
go
sp_changepublication 
@publication='发布名称', 
@property='immediate_sync', 
@value='false'
--在搭建复制时，避免勾选立即初始化和匿名复制
--====================================================================
--在分发服务器上，检查分发库中复制命令数量
USE distribution
GO
SELECT 
A.NAME,
MaxRows = MAX(B.rows)
FROM sys.tables A
INNER JOIN sys.partitions B
ON A.object_id = B.object_id
WHERE A.name = 'MSrepl_transactions'
OR A.name = 'MSrepl_commands'
GROUP BY A.name
--如果分发命令过多时，检查JOB: "分发清除: distribution" 的发生频率，
--默认10分钟触发一次,如果一天清理一次, 上述两个表会积攒大量数据
 
--====================================================================
--清理无订阅的发布
--请慎重
--参考：http://blogs.msdn.com/b/sqlreplication/archive/2013/08/19/transactional-replication-immediate-sync.aspx
SELECT * INTO MSdistribution_agents_bak 
FROM MSdistribution_agents 
WHERE subscriber_id<0
 
SELECT * INTO MSdistribution_history_bak 
FROM MSdistribution_history 
WHERE agent_id IN
(
SELECT id FROM MSdistribution_agents 
WHERE subscriber_id<0
)
 
DELETE FROM MSdistribution_history 
WHERE agent_id IN
(
SELECT id FROM MSdistribution_agents 
WHERE subscriber_id<0
)
 
DELETE FROM MSdistribution_agents 
WHERE subscriber_id<0
 

--====================================================================
--在发布上查看日志读取代理器状态，
--使用复制监视器查看错误日志
--使用复制监视器插入令牌
 
--参考资料：http://blogs.msdn.com/b/apgcdsd/archive/2012/09/07/10347168.aspx