Post reply

Distribution Cleanup Job removes 0 transactions despite all trans being delivered to subscribers

  • September 25, 2009 at 7:07 am

    #214855

    I'm currently experiencing an issue where my distribution database is growing rather rapidly, at around about 4Gb an hour; on investigation into the cleanup job it is apparent that the cleanup is not removing any replicated transactions despite all transactions being replicated to the single subscriber as an example of the output from the job step is as follows:-

    Removed 0 replicated transactions consisting of 0 statements in 3279 seconds (0 rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded.

    Currently the cleanup job is set with the minimum retention time as 0 hours and maximum as 24 hours (i've triple checked to ensure this is hours and not days).

    I've also checked that anonymous subscribers are not allowed to ensure that transactions aren't simply held to the max retention time; though checking back through the cleanup job history the instances where the removed transactions 0 occure for more than the 24 hour period anyway.

    I'm assuming there must be some sort of blocking occurring that is causing the issue, though I would have thought that if this was the case there would be some form of error returned rather than a step succeeded report?

    Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

  • September 26, 2009 at 5:25 am

    #1058731

    I've amended the distrubution settings to have a max retention time of 1 hour and the process now seems to be clearing down the replicated transactions; however obviously this isn't ideal as if for any reason replication were to be held up for an hour those transactions would be cleared without being replicated.

    Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

  • September 28, 2009 at 9:44 am

    #1059065

    I managed to sort the issue as described here:-

    http://blogs.msdn.com/chrissk/archive/2009/07/27/how-replication-setting-immediate-sync-may-cause-transactional-replication-distribution-database-growth.aspx

    Changing the immediate sync setting to FALSE appears to have resolved the problem, and the distribution database is now being cleaned up correctly.

    Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

  • September 28, 2009 at 10:05 am

    #1059073

    Thanks for the update and resolution.

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

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