Analyzing Error log with Trace Flag 1204 turned on

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.
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.
|||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:16Wait-for graph
10/30/04 22:16
10/30/04 22:16Node:1
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Wait List:
10/30/04 22:16Owner:0x23af7620 Mode: S Flg:0x0 Ref:1 Life:00000000
SPID:79 ECID:0
10/30/04 22:16SPID: 79 ECID: 0 Statement Type: SELECT Line #: 25
10/30/04 22:16Input Buf: RPC Event: app_ProductUnit_RetrieveProductUnitData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:71 ECID:0
Ec0x73377558) Value:0x5b0
10/30/04 22:16
10/30/04 22:16Node:2
10/30/04 22:16KEY: 8:2123154609:1 (0502993290b6) CleanCnt:2 Mode: X Flags:
0x0
10/30/04 22:16Grant List 0::
10/30/04 22:16Owner:0x3b4dd720 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:77 ECID:0
10/30/04 22:16SPID: 77 ECID: 0 Statement Type: SELECT Line #: 19
10/30/04 22:16Input Buf: RPC Event: app_ProductUnitAssoc_RetrieveTargetData;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) Value:0x23a
10/30/04 22:16
10/30/04 22:16Node:3
10/30/04 22:16KEY: 8:2123154609:1 (8402c2e9a183) CleanCnt:1 Mode: X Flags:
0x0
10/30/04 22:16Grant List 1::
10/30/04 22:16Owner:0x2d520ea0 Mode: X Flg:0x0 Ref:0 Life:02000000
SPID:71 ECID:0
10/30/04 22:16SPID: 71 ECID: 0 Statement Type: SELECT Line #: 1
10/30/04 22:16Input Buf: RPC Event: sp_executesql;1
10/30/04 22:16Requested By:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0
Ec0x51DF7558) Value:0x3b4
10/30/04 22:16Victim Resource Owner:
10/30/04 22:16ResType:LockOwner Stype:'OR' Mode: S SPID:79 ECID:0
Ec0x5182B558) 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...
>
>
|||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...[vbcol=seagreen]
> 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
> Ec0x73377558) 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
> Ec0x5182B558) 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
> Ec0x51DF7558) 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
> Ec0x5182B558) 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:
|||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...
>
>