Sql-server – Msrel_commands table has 20M rows

replicationsql serversql server 2014

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).

Best Answer

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.

Related Question