Replication dead lock between Publication and Distribution Agent

Question

Post reply

Replication dead lock between Publication and Distribution Agent

Elephant

Mr or Mrs. 500

Points: 511
More actions
May 25, 2011 at 3:38 am

#238757

Hi there is a bit of a weird one.
I have multiple Publication comming from server A, they are being distributed (by server A) onto server B and C.
2 days ago Only on server C, I started getting deadlocks between publications, and Between Publication and the distribution agent.
Those are the kind of error that I am getting on Replication Monitor. "Transaction (Process ID 119) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Transaction (Process ID 119) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 1205)
Get help: http://help/1205"
As the data is being replicated fine to server B and C.
I am trying to resolve the problem without having to re-initialise the replication as it will cause down time.
I have run a dead lock trace but got nothing of value out of it.
I have try to Kill the 1st deadlock process in an attempt for the process not to deadlock.
I have restarted the the Agent both server A and C, hopping that it will sort itself out.
Is there anything that I could use/do to resolve it all without having to re-initialise

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

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

Brandon J Williams Say Hey Kid Points: 699 More actions · Answer 1

Hi Elephant,

What does sp_who2 reveal? High IO/CPU? Is there contention for the distribution database?

If so, I've seen this happen when the distribution database gets very big like > 50GB. If your distribution database is very large ensure that your distribution database cleanup jobs are executing successfully.

Hope this helps.

Elephant Mr or Mrs. 500 Points: 511 More actions · Answer 2

My distribution Db is just over 3Gb, the distribution clean up is running fine every 10 min on server A.

I have attached part of the sp_Who2 you can clearly see the dead lock between transaction.

In this occurance the Distribution Agent is part of the lock but in many other it is not.

SPIDStatusBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramName

61SUSPENDED 94PTS_ReplicaUPDATE 252001/05/2025 21:34DCSQL02_PTS_MarkupTblPublication

66SUSPENDED .PTS_ReplicaUPDATE 2021701/05/2025 21:35DCSQL02_PTS_StopSalesTblPublication

68SUSPENDED .PTS_ReplicaUPDATE 31001/05/2025 21:34DCSQL02_PTS_Markup2TablePublication

70SUSPENDED .PTS_ReplicaUPDATE 188101/05/2025 21:34DCSQL02_PTS_LocationTblPublication

70SUSPENDED .PTS_ReplicaUPDATE 0001/05/2025 21:34DCSQL02_PTS_LocationTblPublication

70SUSPENDED 94PTS_ReplicaUPDATE 0001/05/2025 21:34DCSQL02_PTS_LocationTblPublication

70SUSPENDED .PTS_ReplicaUPDATE 0001/05/2025 21:34DCSQL02_PTS_LocationTblPublication

71SUSPENDED .PTS_ReplicaUPDATE 141001/05/2025 21:34DCSQL02_PTS_UsersTblPublication_Extra2

74SUSPENDED .PTS_ReplicaUPDATE 0001/05/2025 21:34DCSQL02_PTS_Networks_NetworkExchange

76SUSPENDED .PTS_ReplicaUPDATE 94001/05/2025 21:34Replication Distribution Agent

77SUSPENDED .PTS_ReplicaUPDATE 0001/05/2025 21:34DCSQL02_PTS_H2H_HotelIdCodeLnk

85SUSPENDED .PTS_ReplicaUPDATE 223001/05/2025 21:34DCSQL02_PTS_AllocationRoomTypeLnk

90SUSPENDED 94PTS_ReplicaUPDATE 171001/05/2025 21:34Replication Distribution Agent

94SUSPENDED 61PTS_ReplicaUPDATE 61001/05/2025 21:34DCSQL02_PTS_HotelTblPublication

98SUSPENDED .PTS_ReplicaUPDATE 31101/05/2025 21:34DCSQL02_PTS_NetworkRegTblPublication