3

I am desperately looking for some logic or script to check commands in msrepl_commands where number of commands which have already been moved to subscriber but not deleted yet.

I am running the procedure manually:

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

but it is not deleting any rows even though I checked msrepl_commands and found there are 20M rows that all came in between 2016-08-30 00:01:00 AM and 2016-08-31 09:00:00 AM, but if I run distribution cleanup then it should delete top 10K in a loop till the row count reaches 2000.

I am a bit confused how to check those delivered commands (msdistribution_status gives me dilevered and undilevered commands but that is cumulative, and I need current status).

1
  • What is the value of allow_annonymous and immediate_sync in your publications?
    – SQLPRODDBA
    Aug 31, 2016 at 16:21

2 Answers 2

5

I've encountered this issue more times than I can count. For some reason, maybe it's an execution plan difference, if you run it manually and also narrow it down it will complete faster than the job. Figure out the max date in there and then do the cleanup manually:

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 78, @max_distretention = 80
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 76, @max_distretention = 78
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 74, @max_distretention = 76
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 72, @max_distretention = 74

I know this sounds ridiculous, but it is a solution that many of us have had to resort to (I had to google for a solution a few years ago and saw many people needing to do this. One such article). This is one of the reasons why I was so excited to move away from replication when Availability Groups came out.

Make sure you add some monitoring for this condition too that way it doesn't get too far behind when you have to resort to the manual running of sp_MSdistribution_cleanup.

1

The script on this guy's site is one that I've used before.
http://basitaalishan.com/2012/07/25/transact-sql-script-to-monitor-replication-status/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.