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?


more ▼

asked Mar 14, 2010 at 06:25 PM in Default

DaniSQL gravatar image

4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Mar 14, 2010 at 10:28 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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, 2010 at 01:08 AM Rob Farley
Congrats on the 6k grant :)
Mar 15, 2010 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, 2010 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, 2010 at 09:47 AM DaniSQL
No, the traceflag is pretty low cost. I'd just leave it on.
Mar 15, 2010 at 10:05 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Run Profiler and set up a trace to collect information about what's going on, including the Deadlock Graph. This will give you what you need.

And if you prefer, you can make it a server-side trace so that you don't need to have Profiler running for very long.

more ▼

answered Mar 14, 2010 at 07:53 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Thanks Rob. I will set up a server side trace. But is there a way to tell what happened yesterday?(which process is chosen?)
Mar 14, 2010 at 07:59 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

Hi Dani,

i found some useful information at the below links to trace out the dead lock process and how to resolve them using trace flags and SQL profiler.

link text

link text

more ▼

answered Mar 15, 2010 at 12:32 AM

venkatreddy gravatar image

562 28 29 32

Thanks Venkat. That was helpful.
Mar 15, 2010 at 09:49 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 14, 2010 at 06:25 PM

Seen: 5679 times

Last Updated: Mar 15, 2010 at 06:22 AM