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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy