URGENT deadlock question

From: ChrisR (bla_at_noemail.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 10:55:42 -0800

I received 4 deadlocks in about 15 minutes. 3 of them occured at the same
time. Im pretty sure that one was due to a large program that manipulates/
bcp's data in. That one is dealable. So I turned on Profiler. I have a
deadlock template that I use with the following settings:

Locks:
DeadLock
Deadlock Chain

SP's:
RPC Completed
-- Starting
SP Completed
-- Starting

TSQL:
SQL: BatchCompleted
-- BatchStarting
SQL: Statement Completed
SQL: Statement Starting

 According to BOL for DBCC TraceON(1204):

"Trace flags remain enabled in the server until disabled by executing a DBCC
TRACEOFF statement."
I set DBCC TraceON(1204) a month or so ago so I assumed it would still be
on. No reboots since then.

So, Profiler is running and another Deadlock occurs 10 minutes later. I stop
Profiler but there is no Deadlock that I can find. Throuhgtout the Trace,
there is a message that says:

"Some trace events have not been reported to SQL Profiler because the server
has reached its maximum amount of available memory for the process."

So, one of two things is happening:

1. The above message kept Profiler from reporting my Deadlock.
2. 1204 wasnt on.

More info:

In my SQL Error Log, there are about 30 lines of info per deadlock. They
have various Stored Procedure and Select statements for the viewing. Ive
been told that Profiler MUST be running when a deadlock occurs in order to
track it down.

So my questions are:

1. Does 1204 need to be set every time Profiler is started?
2. Cant I figure out the cause of my deadlocks based on the info from the
Error Log? Is it correct that the info from the Error Log Cant be used to
track down a deadlock?

Any other pointers on the matter would be great. 

--
SQL2K SP3
TIA, ChrisR

Relevant Pages

  • Re: SQL Deadlocks Report For Analysis
    ... is setting this trace on flag has some perfomance impact on the SQL ... > Setting up DBCC and then query error log is the way to go as it gives you ... > This logs all deadlock error reports to the sql log. ... >> SPID ...
    (microsoft.public.sqlserver.programming)
  • Re: deadlock question
    ... Yes I did mean the Error Log. ... > Probably only specifying the deadlock victim. ... Profiler can tell you this using ... Trace flag 1204 will report additional deadlock information ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Deadlock?
    ... If you want a concise analysis of the deadlock, there is a DBCC command that ... gives you the nitty-gritties which aren't included in the profiler output. ... This logs deadlock analysis reports to the sql log which you can interpret ...
    (microsoft.public.sqlserver.programming)
  • RE: ICorProfilerInfo::SetFunctionReJIT causes deadlock
    ... The profiling API is intended for diagnostic ... make it impossible to attach a real performance or memory profiler when you ... | Thread-Topic: ICorProfilerInfo::SetFunctionReJIT causes deadlock ... | Content-Type: text/plain; ...
    (microsoft.public.dotnet.framework.clr)
  • =?ISO-8859-1?Q?Re=3A_Deadlock_bei_Tabelle_bef=FCllen?=
    ... den Ladeprozess mitprotokollieren und sich den Deadlock Graphen ausgeben ... Ich werde das mit dem Profiler versuchen - ich habe zwar noch nie ...
    (microsoft.public.de.sqlserver)