question

DaniSQL avatar image
DaniSQL asked

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,

sql-server-2005jobdeadlocktrace
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
Thanks Rob. I will set up a server side trace. But is there a way to tell what happened yesterday?(which process is chosen?)
0 Likes 0 ·
venkatreddy avatar image
venkatreddy answered

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
Thanks Venkat. That was helpful.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.