May 27, 2009 at 2:32 am
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.
May 27, 2009 at 8:02 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply