distribution数据库过大问题

从事件探查器中监控到如下语句执行时间查过 1分钟：

EXEC dbo .sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

该存储过程被“ 分发清除：分发 ”作业每10 分钟调用一次，用户 从分发数据库中删除复制的事务。 停用在最大分发保持期内尚未同步的订阅。

该存储过程主要是删除 MSrepl_commands和MSrepl_transactions 两个表过期数据

查询数据库以及这两个表的数据

可以看到 MSrepl_commands的数据库的空间和数据库空间几乎一致，对其进行删除是性能瓶颈所在。

查询 MSrepl_commands表中未过期数据按照小时分组的命令数。

SELECT T.[publisher_database_id]
,datepart(mm,[entry_time]) 'month'
, datepart(dd,[entry_time]) 'day'
, datepart(hh,[entry_time]) 'hour'
    ,count(C.[xact_seqno]) 'count of commands'
FROM [distribution].[dbo].[MSrepl_transactions](nolock) T 
JOIN [MSrepl_commands](nolock) C 
ON T.[xact_seqno] = C.[xact_seqno]
GROUP BY T.[publisher_database_id]
  ,datepart(mm,[entry_time])
  , datepart(dd,[entry_time])
  , datepart(hh,[entry_time])
order by 1,2,3,4

从结果中看出，定义每次删除 50万数据能够删除有一个小时内的数据，而且可以防止有激增数据情况。

---

解决方案：

1、删除MSrepl_commands 表的存储过程是 sp_MSdelete_publisherdb_trans，删除msrepl_transaction 表的存储过程是 sp_MSdelete_dodelete

分别修改两个存储过程中删除两张表的语句：

DELETE TOP(2000) MSrepl_commands 改为DELETE TOP(500000) MSrepl_commands，两处；delete TOP(5000) MSrepl_transactions 改为delete TOP(500000) MSrepl_transactions两处

2、即使每次删除的数据量很大，但 10分钟执行的频度还是太高，调整为 1小时执行一次。

3、总体上每小时的记录数还是很多，所以需要一次减少保留的时间。根据需求，我改为了 36小时

编辑步骤，将 72改成36

4、第一次建议手工执行删除，并收缩表空间

执行 EXEC dbo .sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36

执行DBCC SHRINKDATABASE ( '数据库' )收缩数据库，收缩后，数据库大小减小为原来一半

 

建议将replicate的恢复模式改为简单，并且开启自动收缩功能。