October 29, 2016 at 9:18 am
Replication has been running for about the last 9 months and now we have had two day's where ever time "EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36" runs it gets hit with a deadlock and gets killed. Any one seen this before? To get it to run I have to shutdown the log reader job.
Msg 1205, Level 13, State 52, Procedure sp_MSdelete_publisherdb_trans, Line 202
Transaction (Process ID 122) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
November 8, 2016 at 1:40 pm
dwilliscp (10/29/2016)
Replication has been running for about the last 9 months and now we have had two day's where ever time "EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 36" runs it gets hit with a deadlock and gets killed. Any one seen this before? To get it to run I have to shutdown the log reader job.Msg 1205, Level 13, State 52, Procedure sp_MSdelete_publisherdb_trans, Line 202
Transaction (Process ID 122) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Hi,
We had similar issues. WE solved it by doing two things:
1. increased the batch size in the cleanup procs (default by microsoft is 2000). Unfortunately u cant pass a parameter and u need to modify the proc those values are hard coded.
2. modify PAGELOCK to ROWLOCK in those procs.
BartL
Replication Blog
November 9, 2016 at 8:16 am
Ok, thanks will try that.
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply