Showing posts with label detemine. Show all posts
Showing posts with label detemine. Show all posts

Monday, March 19, 2012

How to find table from pageno in trace 1204 output?

We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
Value:0x533e9d00 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
Value:0x5344cb60 Cost:(0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
> Value:0x533e9d00 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
> Value:0x5344cb60 Cost:(0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
Posted by exocrinepasl at 2:00 PM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

How to find table from pageno in trace 1204 output?

We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec0x5CD00098)
Value:0x533e9d00 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
Value:0x5344cb60 Cost0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec0x5CD00098)
> Value:0x533e9d00 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
> Value:0x5344cb60 Cost0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>
Posted by exocrinepasl at 1:59 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)