another deadlock question

Is there a way to run Profiler to trap just deadlock info and not all the
info on the box? I set Error 1205 in the filters but still I got lots of
extra info.
--
SQL2K SP3
TIA, ChrisRChris,
Profiler allows you to trace the following deadlock related events:
- Lock:Deadlock
- Lock:Deadlock Chain
Personally, I find the deadlocking info you can get in the errorlog from
trace flag 1205 much more helpful. To turn on this traceflag you just run:
DBCC TRACEON (3605, 1205, -1)
1205 will return info regarding the nodes involved in a deadlock
(when/if they happen). 3605 will log that info to the errorlog. -1
will apply the trace flags to all sessions (i.e. at the server level)
rather than just for the current connection. Turning on the trace flags
this way, however, is not permanent. They'll be reset (i.e. turned off)
again the next time the you stop & restart SQL. To turn them on at
start time, add the following startup parameters to the server using SQLEM:
-T3605 -T1205
Unfortunately these particular trace flags are not documented in SQL BOL
(but they're pretty well known). But BOL does have a little info about
DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
options).
Cheers,
Mike
ChrisR wrote:
> Is there a way to run Profiler to trap just deadlock info and not all the
> info on the box? I set Error 1205 in the filters but still I got lots of
> extra info.
> --
> SQL2K SP3
> TIA, ChrisR
>|||> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
the extra information: 1205 export the analysis for every deadlock search,
which is a lock scan looking for a loop, regardless if it finds one. Only
use 1205 if you are have serious deadlock issues and need preemptive
information; otherwise, 1204 should give you sufficient information from any
detected deadlock candidates.
Sincerely,
Anthony Thomas
"ChrisR" <bla@.noemail.com> wrote in message
news:u8DRDRk5EHA.1396@.tk2msftngp13.phx.gbl...
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||Yep, I always get 1204 & 1205 mixed up - I usually just turn them on one
at a time and you can tell in about 3 seconds which is the right one by
looking at the errorlog (I was going from memory this time). But, yes,
1204 is the traceflag I meant (otherwise, with 1205, the errorlog fills
up rapidly with fairly useless deadlock cycle detection crap).
My apologies for the errata.
Basically, Chris, profiler will not give you enough info to troubleshoot
your deadlocks (at least not without tracing pretty much everything,
which makes finding the info you're after very difficult). You're
better off turning on the traceflag and checking the errorlog next time
a deadlock occurs.
(It's been my observation that the 2 most common questions on
microsoft.public.sqlserver.server are 1) resolving/troubleshooting
deadlocks and 2) shrinking the transaction log.)
Cheers,
Mike.
AnthonyThomas wrote:
> The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
> the extra information: 1205 export the analysis for every deadlock search,
> which is a lock scan looking for a loop, regardless if it finds one. Only
> use 1205 if you are have serious deadlock issues and need preemptive
> information; otherwise, 1204 should give you sufficient information from any
> detected deadlock candidates.
> Sincerely,
>
> Anthony Thomas
>