Deadlocks and trace 1204

We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:

> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadloc
k
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...[vbcol=seagreen]
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>