Deadlock diagnosis

We are experiencing the following deadlock error on a SQL Server 2000
system:
"Transaction (Process ID 53) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."
I've done some research to see how I can track where the deadlock is
occurring and I've come across a couple of recommendations to use DBCC
traces 1204 & 1205. I'm trying to track where exactly in our system this is
occurring as we are interacting with 10 different tables within a
transaction.
Can someone explain the full process to using tracing to diagnose deadlocks
as I've never done this before? Also, is this the best option for SQL
Server 2000?
Thanks in advance.Cipher wrote:
> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun
> the transaction."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system
> this is occurring as we are interacting with 10 different tables
> within a transaction.
> Can someone explain the full process to using tracing to diagnose
> deadlocks as I've never done this before? Also, is this the best
> option for SQL Server 2000?
> Thanks in advance.
Using trace flags and tracing are two different things. To use trace
flags, you need to turn them on using DBCC TRACEON/TRACEOFF. You can
send deadlock info to the error log using:
DBCC TRACEON (1204,3605,-1)
You can also monitor deadlocks using Profiler or running a server-side
trace and including the two deadlock events (Lock:Deadlock and
Lock:DeadlockChain). If you use Profiler to monitor these events (even
Lock:Deadlock by itself will do) along with using the 1204 trace flag,
you'll know when to look in the error log.
If you want to use a server-side trace instead of trace flags, you'll
need to include a few SQL/SP events to make sure you know what each SPID
involved in the deadlock was running at the time. You can do this, but
you should use a server-side trace instead of using Profiler because of
the added event collection activity. Add SQL:StmtStarting, RPC:Starting,
and SP:StmtStarting to the two deadlock events for comprehensive event
collection. If you can filter these results to a set of users or
applications you know are having the problem, that can limit the
collection somewhat.
To run a server-side trace, you can create the trace in Profiler and
script it out using the File - Script Trace option. You need to have the
trace save activity to a file on the server itself (not a network
share). Once it's running, you'll need to stop it manually using
sp_trace_setstatus. You can view the results in Profiler orby using the
fn_trace_gettable function.
I would try the small Profiler trace along with trace flag 1204 first.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
In addition to Davids post check out
http://support.microsoft.com/kb/271509/EN-US/
John
"Cipher" wrote:

> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction
."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system this
is
> occurring as we are interacting with 10 different tables within a
> transaction.
> Can someone explain the full process to using tracing to diagnose deadlock
s
> as I've never done this before? Also, is this the best option for SQL
> Server 2000?
> Thanks in advance.
>
>
>|||When you are able to get the trace dump into the log file, you might want to
check "Troubleshooting Deadlocks" in BOL
inorder to interpret the dump file.
Gopi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:90FB9903-EECE-4BC8-87FF-5FF09AFEFB3B@.microsoft.com...
> Hi
> In addition to Davids post check out
> http://support.microsoft.com/kb/271509/EN-US/
> John
> "Cipher" wrote:
>