locked
Distribution size growing. RRS feed

  • Question

  • On a DB server (Publisher & distributor is same) Distribution DB grew to it's full until it used full of space of drive. I had to extend drive and make replication (transnational) sync manually.

    Now below is the space of distribution DB, I have few question I would like to clear.

    1. As I can see MDF file of distribution shows around 83 GB is free, does it mean distribution cleanup job worked successfully ?

    2. Will clean-up job reduce size of actual MDF file from total MB ? Or I have to manually shrink MDF ?

    3. This was fine till Friday, suddenly distribution grow to full, how can I correlate the reason of this frequent growth. 



    Wednesday, June 17, 2020 8:48 AM

Answers

  • Hi V Jay Rana,

    The Distribution Cleanup job, which runs on the Distributor Server is responsible for cleaning up the transactions and commands based on the specified transaction retention period, and does the cleanup of the expired subscriptions on the distributor.

    After running the cleanup job , many rows will be removed from related tables and many transactions will be deleted. Then the size of MDF file will reduce.

    You could also shrink the MDF file using DBCC SHRINKFILE if necessary but not strongly recommended.

    To troubleshoot sudden distribution grow issue, please firstly check whether Subscribers is down or whether the cleanup job is running properly.

    Please also refer below links for more details about troubleshooting and reducing the size of Distribution DB:

    Reducing the size of the distribution database & improving transactional replication performance: Part 1

    SQL Server – Controlling Growth of a Distribution Database

    Distribution database size is huge

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com





    Thursday, June 18, 2020 9:38 AM

All replies

  • Hi V Jay Rana,

    The Distribution Cleanup job, which runs on the Distributor Server is responsible for cleaning up the transactions and commands based on the specified transaction retention period, and does the cleanup of the expired subscriptions on the distributor.

    After running the cleanup job , many rows will be removed from related tables and many transactions will be deleted. Then the size of MDF file will reduce.

    You could also shrink the MDF file using DBCC SHRINKFILE if necessary but not strongly recommended.

    To troubleshoot sudden distribution grow issue, please firstly check whether Subscribers is down or whether the cleanup job is running properly.

    Please also refer below links for more details about troubleshooting and reducing the size of Distribution DB:

    Reducing the size of the distribution database & improving transactional replication performance: Part 1

    SQL Server – Controlling Growth of a Distribution Database

    Distribution database size is huge

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com





    Thursday, June 18, 2020 9:38 AM
  • refer following article step by step

    https://social.technet.microsoft.com/wiki/contents/articles/52607.sql-server-troubleshooting-distribution-database-size-is-increasing.aspx


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Sunday, June 21, 2020 1:48 PM
  • Hi V Jay Rana,

    Have you found out any root cause or solution?

    To reduce the size of distribution database, please try to change the retention days from default 24 hours to 120 hours and run the clean-up job. Then after that shrink the database and re-index all tables in the distribution database and run the clean up job again.

    Slowdown Distribution History Cleanup for Troubleshooting

    Troubleshooting Slow SQL Server Replication issue due to Distributor Database Growth

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com





    Tuesday, June 23, 2020 8:30 AM
  • Also disable immediate sync on your publications.
    • Proposed as answer by OtisLutz Wednesday, June 24, 2020 2:05 PM
    Wednesday, June 24, 2020 1:21 PM
    Answerer
  • Its helpful. Thanks a lot. I was facing the same issues.
    Wednesday, June 24, 2020 2:05 PM
  • Hi V Jay Rana,

    Could you please let us know if any update?

    Please verify all the solutions provide and mark the answer if helpful. Thanks.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 26, 2020 3:01 AM
  • any update on issue?

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Saturday, June 27, 2020 6:42 AM