PROBLEMS WITH REPLICATION DISTRIBUTION HISTORY

• muruecos

  SSC Veteran

  Points: 295

  More actions

  May 27, 2009 at 2:32 am

  #210049

  Hi All,

  I have a transactional replication.

  In the "Replication Monitor", I get the error:

  first:

  "agent WS3S2-RAO_Diferido_1-PUB_RAO_1_TRPFTCpr-ws3s1-149 scheduled for

  retry. Transaction (Process ID %d) was deadlocked on %.*ls resources with

  another

  process and has been chosen as the deadlock victim. Rerun the transaction".

  second:

  "The agent 'WS3S2-RAO_Diferido_1-PUB_RAO_1_TRPFTCpr-WS3S1-149' is tring the

  operation after error. 365 attempts execute. Consult historial´s agent´s jobs

  to get more details."

  In the "Log File Viewer" , I get the error:

  "agent WS3S2-RAO_Diferido_1-PUB_RAO_1_TRPFTCpr-ws3s1-149 scheduled for

  retry. Transaction (Process ID %d) was deadlocked on %.*ls resources with

  another

  process and has been chosen as the deadlock victim. Rerun the transaction",

  with source: spid89.

  I executed sql server profiler with events:deadlock graph, deadlock and

  deadlock chain but I didn´t get information about deadlocks

  (Lock:deadLock......). Why don´t I get this information if I have a

  deadLock??.

  I´m researching (sql server profiler´s trace) and I get that spid89 is:

  ApplicationName: Replication Distribution History

  TextName: exec sp_MSadd_distribution_history 149,5,Transaction (Process ID

  56) was deadlocked on %.*ls resources with another

  process and has been chosen as the deadlock victim. Rerun the

  transaction.',0x0000051D00002548000400000000,2,12,0,1,1,0x0000051D00002534001200000000,1,1,1

  and

  TextName: exec sp_MSadd_repl_error 3046462,0,0,N'WS3S1',N'0',Transaction

  (Process ID 56) was deadlocked on %.*ls resources with another

  process and has been chosen as the deadlock victim. Rerun the

  transaction..',NULL,1,N'

  El Agente de distribución de SQL ha detectado un error.

  Publicador: WS3S2

  Base de datos del publicador: RAO_Diferido_1

  Publicación: PUB_RAO_1_TRPFTCpr

  Suscriptor: WS3S1

  Base de datos del suscriptor: RAO_Diferido_1

  ',1

  What the problem?? and How can I solve??

  Thank you, very much.

• Vinay Thankur

  SSCarpal Tunnel

  Points: 4178

  More actions

  May 27, 2009 at 8:02 am

  #1000615

  For Deadlock you have to enable the Trace

  http://msdn.microsoft.com/en-us/library/ms178104(SQL.90).aspx

  And for trubleshooting:

  http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/05/617960.aspx

  My opinion:

  1. change the schedule for distributor so as to run could be after 1hr(if possible)

  2. as you said its a reporting database try to use (NOLOCK) hint *this will give dirty reads but here it should be ok.

  3. as usual for all blocking/deadlock short your transactions.

  Cheers.

  Vinay

  Thanx.
  Vinay

  http://rdbmsexperts.com/Blogs/
  http://vinay-thakur.spaces.live.com/
  http://twitter.com/ThakurVinay