deadlocked on thread

Question

deadlocked on thread

schste0

Old Hand

Points: 374
More actions
September 13, 2010 at 2:28 am

#229222

Hi, we run SQL 2005 9.00.3042.00 Enterprize Edition and sometimes a job running at night generates the following entry in the Job history:
Transaction (Process ID xx) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
Is there any chance to get more detailed information without running a trace?
Additional Information:
wait_typewait_time_spctrunning_pct
CXPACKET22214734.3848.7548.75
SQLTRACE_BUFFER_FLUSH8255295.8118.1266.87
PAGEIOLATCH_SH4041934.388.8775.74
WAITFOR3082086.036.7682.51
OLEDB1520643.613.3485.84
ASYNC_NETWORK_IO1144583.592.5188.35
BACKUPBUFFER920445.192.0290.37
and MaxDegreeOfParallelism is 0

Viewing 6 posts - 1 through 5 (of 5 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

you can run the profiler to capture the deadlock event and deadlock graph for better clearity.

Just run the profile with only deadlock event and deadlock graph for specific database and next morning you can analyse it.

OR

if its getting faield with same reason everytime then you can run profiler anytime and capture the deadlocks

----------
Ashish

schste0 Old Hand Points: 374 More actions · Answer 2

The point is that it occours only rarely and when it occours the night job fails. So i would like to avoid monitoring for the next 10 weeks and would like to analyize and correct the cause before this issue pops up again. BR, Stephan

crazy4sql SSCoach Points: 19590 More actions · Answer 3

then i think. like i suggested earlier, you need to run the trace to capture the deadlock event using profiler, if that is possible.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You may want to call MS customer support on this. Deadlock on communication buffer is an odd one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

schste0 Old Hand Points: 374 More actions · Answer 5

schste0

Old Hand

Points: 374

September 13, 2010 at 3:05 am

#1219908

Thx, I will do so. BR, Stephan