How can I Identify all processes involved in a deadlock?

Hi All,

I have these job running early morning everyday and at least once a week the job fails with the following error message:

Executed as user: xxx\Administrator. Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205) [SQLSTATE 01000] (Error 7312). The step failed.

My question is how can I find the other process/job that is chosen over these job by sql server? what should I do to prevent SQL Server choosing these job as a deadlock victim?

Thanks,

asked Mar 14 '10 at 06:25 PM in Default

DaniSQL
4.8k ● 30 ● 35 ● 39

(comments are locked)

3 answers:

Did you have the traceflags set? In SQL Server 2000, traceflag 1204, in 2005/2008, traceflag 1222. These will capture deadlock information into the error log when a deadlock occurs. I prefer them to using a server side trace because they're things you set once and then don't have to worry about managing afterwards.

answered Mar 14 '10 at 10:28 PM

Grant Fritchey ♦♦
62.3k ● 12 ● 20 ● 66

Ooh! 6000 points for you. And yes, this is very useful. I find myself preferring a trace, because there's often times when I want to look at what was running when users reported things were slow, etc.

Mar 15 '10 at 01:08 AM Rob Farley

Congrats on the 6k grant :)

Mar 15 '10 at 05:56 AM Matt Whitfield ♦♦

Thanks. Yeah, I like having the trace too, but I don't always want to manage the mess of running them 24/7.

Mar 15 '10 at 06:52 AM Grant Fritchey ♦♦

Thank you Grant. The trace was off and I turn it on now. Does turning the trace flag on has a high performance overhead? Is the best practice to turn it off once I figure out what is going on?

Mar 15 '10 at 09:47 AM DaniSQL

No, the traceflag is pretty low cost. I'd just leave it on.

Mar 15 '10 at 10:05 AM Grant Fritchey ♦♦

(comments are locked)