Post reply

Distribution database not getting cleaned up

  • July 12, 2017 at 1:52 pm

    #338213

    Hi 

    I am using transaction replication in our environment

    The distribution database I see is not getting cleaned up. It has grown to 55 GB

    I checked that that allow_anonymous is 0 and immediate_sync is 0 too

    Also there are no orphan subscriptions in MSsubscriptions. Everything is with status 2

    Can someone please let me know what might be preventing distribution database clean up ?

  • July 12, 2017 at 1:53 pm

    #1950631

    Also when I manually run the clean up stored procedure, it just says it removed 0 transactions with 0 commands

    Any kind of help is highly appreciated

  • July 12, 2017 at 2:09 pm

    #1950637

    Run this:

    select * from MSdistribution_history
    where xact_seqno = 0x00000000000000000000000000000000

    This will give you the agent which is causing an issue. Drop the subscription for this publication and then run the cleanup job again.

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • July 12, 2017 at 2:52 pm

    #1950641

    Often times it's something with the clean up job or retention settings. So things to start checking:
    Did you check the retention period for the distributor? Right click on the replication folder and select distributor properties.
    Check the same in the Distribution Cleanup job - the second parameter is the max retention in hours.
    Then check for the oldest entry time in the MSrepl_transactions table in the distribution database. All of those should be set the same and the value should be whatever you need. The default is 72 hours.
    Also check the publication retention times as well. Along with that, you would want to check the expired subscriptions clean up job - make sure that's running regularly.  
    And then also check the MSrepl_errors MSrepl_command tables in the distribution database.

    I'd also try to run the cleanup script from the job manually in the distribution database itself. And pay attention to how long it takes. Disable the clean up job before running that manually so they don't start blocking each other. And don't forget to re-enable it even though it doesn't seem to be doing much right now.

    Sue

  • July 12, 2017 at 7:04 pm

    #1950662

    Thanks Sue for replying 

    My issue got resolved.

    It turned out to me one of the DT agents was in a stopped state at the same time during which we were seeing the oldest un-cleaned command in msrepl_commands.

    Enabling and running that DT agent and then running distribution clean up brought the database size down

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply