none
Distribution clean up: distribution over running

    Question

  • The 'Distribution clean up: distribution' job has been set to run every 10 minutes but lately, it seems to run continuously. The things I have looked at so far are:

    1. If the 'Transaction retention' is set the same as whats on the script on the job e.g. EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

    2. The size of the [dbo].[MSrepl_commands] table, which understandably is growing as a result of the job not completing

    Is there anything which could be done to resolve this issue?

    Thank you in advance!

    Thursday, June 21, 2018 10:53 AM

All replies

  • What SQL versions are your publisher, distributor, and subscriber?

    Is the subscriber in sync with the publisher? 

    Can you try to run the same command the cleanup job runs, manually from SSMS? See if it returns any messages.

    When the cleanup job is in progress, can you monitor and see if there's anything blocking it or what the wait type for that SPID is?

    Denny has some tips on modifying the default setting, but be careful when doing so.

    https://itknowledgeexchange.techtarget.com/sql-server/reducing-locking-by-distribution-clean-up-job/

    Also, see if the following helps:

    https://www.mssqltips.com/sqlservertip/1823/troubleshooting-slow-sql-server-replication-issue-due-to-distributor-database-growth/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, June 21, 2018 1:00 PM
  • What SQL versions are your publisher, distributor, and subscriber?

    All 2012 Enterprise editions 

    Is the subscriber in sync with the publisher? 

    Yes there are sync

    Can you try to run the same command the cleanup job runs, manually from SSMS? See if it returns any messages.

    I'm assuming I would have to first disable the job?

    When the cleanup job is in progress, can you monitor and see if there's anything blocking it or what the wait type for that SPID is?

    There is blocking from the "Repl-LogReader..." process which cannot be killed, so I'm not sure how that could be fixed                              

    Thank you


    • Edited by ti2 Friday, June 22, 2018 9:52 AM
    Friday, June 22, 2018 8:29 AM
  • Yeah, stop the distribution cleanup job and run the command manually from SSMS.

    Also, what Service Packs are they on? You might want to bring them to the latest service pack.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, June 22, 2018 9:14 AM
  • Thank you.

    The service pack is, Service pack 1

    Regarding the "blocking process" which is the Repl-LogReader job, what could be done in order to resolve it please?

    Friday, June 22, 2018 12:07 PM
  • Thank you.

    The service pack is, Service pack 1

    Regarding the "blocking process" which is the Repl-LogReader job, what could be done in order to resolve it please?

    I would first start with applying the latest service pack. The latest for SQL 2012 is SP4 and you are three SPs behind already. 

    For the issue in question, you can try #1 below if it doesn't help, you may go with #2.

    1) Stop the log reader job and let the distribution cleanup job run. See if it progresses fine. It may take time to finish as it has huge data to delete. Note: The log file cannot be truncated because the log records are not processed by the log reader agent (since it'd be off), so you might see an increase in the log file size so make sure that log reader isn't turned off indefinitely. 

    2) Turn immediate_sync to False for the publication (See this)


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, June 22, 2018 8:44 PM
  • Check here: https://bartoszlewandowski.blog/2017/05/12/when-distribution-cleanup-not-behaving/

    Maybe this will help you. I had that few times on very busy publishers.

    • Proposed as answer by codandn Wednesday, June 27, 2018 3:58 AM
    Tuesday, June 26, 2018 8:21 PM