如何解決在 TRANSACTIONAL REPLICATION的DISTRIBUTION資料庫的MDF過大的問題

如何清除REPLICATION的DISTRIBUTION資料庫

最近發現DISTRIBUTION的MDF過大，後來發現是因為『清除散發: distribution』這個SQL JOB執行失敗的而造成無法刪除MSrepl_commands這個table的資料，而該JOB所產生的『訊息20015，層級16，狀態1，程序sp_MSreplremoveuncdir，行8』錯誤訊息解決方法請參考該網址，解決該問題後，重新執行該job後， MSrepl_commands這個table的資料就可以刪除了，以下是我解決問題DISTRIBUTION資料庫過大問題的步驟：

(1)    確認散發者的交易與紀錄保留時間

(2)    執行SELECT TOP 10 OBJECT_NAME(id) OBJECTNAME,reserved,rowcnt FROM SYSINDEXES ORDER BY 2 DESC;查看MSrepl_commands這個TABLE的容量，如果太大要執行sp_MSdistribution_cleanup使用清除MSrepl_commands這個TABLE，執行指令為：EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 48

執行結果：

(3)    將發行集的訂閱過期從永遠不會過期改為間隔12小時未同步就卸除。

另外若有多個發行集可以使用下列SQL指令來觀察間隔時間

SELECT DISTINCT

srv.srvname publication_server

, a.publisher_db

, p.publication publication_name

, p.retention

, ss.srvname subscription_server

, s.subscriber_db

FROM MSArticles a

JOIN MSpublications p ON a.publication_id = p.publication_id

JOIN MSsubscriptions s ON p.publication_id = s.publication_id

JOIN master..sysservers ss ON s.subscriber_id = ss.srvid

JOIN master..sysservers srv ON srv.srvid = p.publisher_id

JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id

AND da.subscriber_id = s.subscriber_id

ORDER BY p.retention

執行結果如下：

執行上述步驟後使用DBCC SHRINKDATABASE 來壓縮資料庫即可。


參考網址