Freigeben über

Replication: Distribution Cleanup

  • Artikel

Manual Cleanup

What can one do when the distribution cleanup job is unable to cleanup the transactions in the distribution database?

If this should occur, one should analyze why this is happening as the cleanup job is essential to the replication process. However should we need to clean the old data up as quickly as possible due to the impact it is having, then:

Regarding the idea of cleaning up the oldest entries first:

Usually the cleanup job calls the following stored procedure:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

  • @max_distretention determines the cutoff date, specified in hours
  • Get a rough estimate on how long the cleanup issue is occurring
  • Let’s assume it’s 2 weeks = 14 days = 336 hours

Let’s clean up everything that is older than 14 days but nothing that is younger:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 336, @max_distretention = 360

  • If this is successful in a timely manner, then continue with an earlier point in time:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 310, @max_distretention = 336

  • Again if this works, get an earlier time span.
  • If any of this is taking too long, investigate for blocking (sp_who2). Either go back further to the past, or make the time span a bit shorter.

The should always be tested on a test environment before being run / executed on a production server. The regular cleanup job should be stopped for any interference.

Note:

Max is the maximum amount of time commands will be stored in the distribution database IF subscribers have not picked them up, or if you are using anonymous subscribers.

Min, is the minimum amount of time commands will be stored in the distribution after they have been replicated to named subscribers.

Comments