Re: Analyzing Error log with Trace Flag 1204 turned on
From: MikeInOakville (MikeInOakville_at_discussions.microsoft.com)
Date: 12/01/04
- Next message: Andrew J. Kelly: "Re: My Database doesnt appear to have a transaction log!"
- Previous message: Allen White: "Licensing question - Multiple Instances on One Server"
- In reply to: Kalen Delaney: "Re: Analyzing Error log with Trace Flag 1204 turned on"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 1 Dec 2004 06:51:02 -0800
Hi Kalen,
Ok, sounds good. I have read the appropriate section out of Inside SQL
Server 2000. One last (hopefully) follow up question for you. Do you have a
sample profiler template that you commonly use to assist in troubleshooting
deadlocks? My dilemna is this....is there a way to show the queries leading
up to a deadlock only from the spids involved in the deadlock? I don't want
to capture all database SQL activity because this becomes very large very
quick.
"Kalen Delaney" wrote:
> Hi Mike, and thanks, :-)
>
> If the truth be known, I usually don't use the traceflag output for the real
> troubleshooting. If I am trying to track down deadlocks, I hae this flag
> enabled, and I also have a trace running to capture deadlock events. I use
> this traceflag output only to get the spids and the time, and then I can
> find what I need in the trace output, which shows me the statements that led
> up to the deadlock. Usually that's enough to figure it out.
>
> The keys can be either the ones being waited on or the ones requested. It
> depends where in the output the line occurs.
> I have quite a bit of info on interpreting this output and understand lock
> resources in Inside SQL Server 2000, and in my ebook on Troubleshooting
> Locking and Blocking.
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "MikeInOakville" <MikeInOakville@discussions.microsoft.com> wrote in message
> news:2398D345-8736-48D6-A1E2-816AAF2E8383@microsoft.com...
> >
> > Thank you for the quick response (BTW, I enjoy your articles in SQLMag).
> > Here is an snippet from my error log:
> >
> > 10/30/04 22:16 ...
> > 10/30/04 22:16
> > 10/30/04 22:16 Wait-for graph
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:1
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Wait List:
> > 10/30/04 22:16 Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
> > SPID:79 ECID:0
> > 10/30/04 22:16 SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnit_RetrieveProductUnitData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
> > Ec:(0x73377558) Value:0x5b0
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:2
> > 10/30/04 22:16 KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 0::
> > 10/30/04 22:16 Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:77 ECID:0
> > 10/30/04 22:16 SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
> > 10/30/04 22:16 Input Buf: RPC Event:
> > app_ProductUnitAssoc_RetrieveTargetData;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> > 10/30/04 22:16
> > 10/30/04 22:16 Node:3
> > 10/30/04 22:16 KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X
> > Flags:
> > 0x0
> > 10/30/04 22:16 Grant List 1::
> > 10/30/04 22:16 Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
> > SPID:71 ECID:0
> > 10/30/04 22:16 SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
> > 10/30/04 22:16 Input Buf: RPC Event: sp_executesql;1
> > 10/30/04 22:16 Requested By:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
> > Ec:(0x51DF7558) Value:0x3b4
> > 10/30/04 22:16 Victim Resource Owner:
> > 10/30/04 22:16 ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
> > Ec:(0x5182B558) Value:0x23a
> >
> > I do not see enough information in BOL around what the information on the
> > lines that start with KEY represent. Are they locks currently held or
> > locks
> > requested? Also I do not understand what information is included on the
> > line
> > starting with ResType (i.e. what are ResType and Stype) I'm finding it
> > very
> > challenging to look at this log and deduce the sequence of the calls and
> > locks that led to my deadlock.
> >
> > If you have any further advice or links I'd appreciate it.
> >
> > Mike
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> The article "Troubleshooting Deadlocks" in Books Online explains what all
> >> these terms mean.
> >>
> >> --
> >> HTH
> >> ----------------
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "MikeInOakville" <MikeInOakville@discussions.microsoft.com> wrote in
> >> message
> >> news:45642E4E-84C6-4DBE-99C9-3008291F3160@microsoft.com...
> >> >
> >> > Hello, I have what I believe should be a fairly simple question. I
> >> > have a
> >> > server with trace flag 1204 turned on. I have entries in this log that
> >> > show
> >> > deadlock information. i'm looking for information about how to analyze
> >> > the
> >> > data in this log. It contains a log of information about things such
> >> > as
> >> > Grant lists, keys, owners etc. I need information to explain what I'm
> >> > looking at.
> >>
> >>
> >>
>
>
>
- Next message: Andrew J. Kelly: "Re: My Database doesnt appear to have a transaction log!"
- Previous message: Allen White: "Licensing question - Multiple Instances on One Server"
- In reply to: Kalen Delaney: "Re: Analyzing Error log with Trace Flag 1204 turned on"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
