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

avatar image

4.9k 33 39 43

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar image

Rob Farley
5.8k 16 22 28

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

avatar image

562 29 31 36

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.

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: 6642 times

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

Copyright 2016 Redgate Software. Privacy Policy