Help

Can anyone tell me what this is :
PAG: 7:1:165015
Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
ThanxsSQL Server stores tables, indexes etc internally on "pages", which are 8kb
disk structures in SQL 7 / 2000. Additionally, pages are allocated in
"Extents", which are groups of 8 pages (64kb).
PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so database #7, file #
1 & page # 165015 in the internal storage system.
Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers to database # 8,
Object # 1653632984 & Index #2. You can identify the index by looking up
that objectid in the sysobjects system table.. It's likely a table & the
index is the second index on that table.
This type of information is generally displayed in locking analysis tools
such as deadlock graphs or output from sp_lock. Where are you seeing it? And
do you have a wider problem?
Regards,
Greg Linwood
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> Can anyone tell me what this is :
> PAG: 7:1:165015
> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> Thanxs|||I have a deadlock reported (DBCC Trace on 1204) where
the "lock" is this:
PAG: 7:1:165015 and
PAG: 7:1:183854
How can i find out what "thing" is that. Ex:
KEY 8:1653632984:2
I can find the table and index associated with this
number.. how can i do that for PAG: 7:1:183854
very grateful for your help
>--Original Message--
>SQL Server stores tables, indexes etc internally
on "pages", which are 8kb
>disk structures in SQL 7 / 2000. Additionally, pages are
allocated in
>"Extents", which are groups of 8 pages (64kb).
>PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
database #7, file #
>1 & page # 165015 in the internal storage system.
>Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers
to database # 8,
>Object # 1653632984 & Index #2. You can identify the
index by looking up
>that objectid in the sysobjects system table.. It's
likely a table & the
>index is the second index on that table.
>This type of information is generally displayed in
locking analysis tools
>such as deadlock graphs or output from sp_lock. Where are
you seeing it? And
>do you have a wider problem?
>Regards,
>Greg Linwood
>SQL Server MVP
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:059e01c3c54c$68883850$a101280a@.phx.gbl...
>> Can anyone tell me what this is :
>> PAG: 7:1:165015
>> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
>> Thanxs
>
>.
>|||Can you post the full deadlock graph? This is the output from trace 1204.
There is better information in other parts of that output that will help us
get closer to your problem faster.
Trying to reverse back from page # to the cause of the deadlock is doing it
the hard way. The graph will reveal importantly, what type of dealock is
occurring & where. I'm on Australian time, so if you can do this within an
hour or so I'll look again tonight, otherwise hopefully someone else can
help too..
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> I have a deadlock reported (DBCC Trace on 1204) where
> the "lock" is this:
> PAG: 7:1:165015 and
> PAG: 7:1:183854
> How can i find out what "thing" is that. Ex:
> KEY 8:1653632984:2
> I can find the table and index associated with this
> number.. how can i do that for PAG: 7:1:183854
> very grateful for your help
> >--Original Message--
> >SQL Server stores tables, indexes etc internally
> on "pages", which are 8kb
> >disk structures in SQL 7 / 2000. Additionally, pages are
> allocated in
> >"Extents", which are groups of 8 pages (64kb).
> >
> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> database #7, file #
> >1 & page # 165015 in the internal storage system.
> >
> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID) refers
> to database # 8,
> >Object # 1653632984 & Index #2. You can identify the
> index by looking up
> >that objectid in the sysobjects system table.. It's
> likely a table & the
> >index is the second index on that table.
> >
> >This type of information is generally displayed in
> locking analysis tools
> >such as deadlock graphs or output from sp_lock. Where are
> you seeing it? And
> >do you have a wider problem?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> Can anyone tell me what this is :
> >>
> >> PAG: 7:1:165015
> >>
> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >>
> >> Thanxs
> >
> >
> >.
> >|||Can you tell me what you get from this and how so i can
learn for the furtue:
Deadlock encountered ... Printing deadlock information
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Wait-for graph
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:1
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
(1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
2003-12-17 03:55:49.99 spid4 Grant List 0::
2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
Statement Type: INSERT Line #: 35
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
GetSubscription;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
Value:0xef529e0 Cost:(0/84)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:2
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
(e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
2003-12-17 03:55:49.99 spid4 Wait List:
2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
Statement Type: INSERT Line #: 35
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
GetSubscription;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
Value:0x7e27b780 Cost:(0/0)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 Node:3
2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
(e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
2003-12-17 03:55:49.99 spid4 Grant List 3::
2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
Statement Type: UPDATE Line #: 23
2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
SetEventSentDate;1
2003-12-17 03:55:49.99 spid4 Requested By:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
Value:0x3b4d7e80 Cost:(0/0)
2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
2003-12-17 03:55:49.99 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
Value:0x3b4d7e80 Cost:(0/0)
2003-12-17 03:55:49.99 spid4
2003-12-17 03:55:49.99 spid4 End deadlock search
41725 ... a deadlock was found.
2003-12-17 03:55:49.99 spid4 --
--
Cheers :)
>--Original Message--
>Can you post the full deadlock graph? This is the output
from trace 1204.
>There is better information in other parts of that output
that will help us
>get closer to your problem faster.
>Trying to reverse back from page # to the cause of the
deadlock is doing it
>the hard way. The graph will reveal importantly, what
type of dealock is
>occurring & where. I'm on Australian time, so if you can
do this within an
>hour or so I'll look again tonight, otherwise hopefully
someone else can
>help too..
>Regards,
>Greg Linwood
>SQL Server MVP
><anonymous@.discussions.microsoft.com> wrote in message
>news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
>> I have a deadlock reported (DBCC Trace on 1204) where
>> the "lock" is this:
>> PAG: 7:1:165015 and
>> PAG: 7:1:183854
>> How can i find out what "thing" is that. Ex:
>> KEY 8:1653632984:2
>> I can find the table and index associated with this
>> number.. how can i do that for PAG: 7:1:183854
>> very grateful for your help
>> >--Original Message--
>> >SQL Server stores tables, indexes etc internally
>> on "pages", which are 8kb
>> >disk structures in SQL 7 / 2000. Additionally, pages
are
>> allocated in
>> >"Extents", which are groups of 8 pages (64kb).
>> >
>> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
>> database #7, file #
>> >1 & page # 165015 in the internal storage system.
>> >
>> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
refers
>> to database # 8,
>> >Object # 1653632984 & Index #2. You can identify the
>> index by looking up
>> >that objectid in the sysobjects system table.. It's
>> likely a table & the
>> >index is the second index on that table.
>> >
>> >This type of information is generally displayed in
>> locking analysis tools
>> >such as deadlock graphs or output from sp_lock. Where
are
>> you seeing it? And
>> >do you have a wider problem?
>> >
>> >Regards,
>> >Greg Linwood
>> >SQL Server MVP
>> >
>> >
>> >":)" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
>> >> Can anyone tell me what this is :
>> >>
>> >> PAG: 7:1:165015
>> >>
>> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
>> >>
>> >> Thanxs
>> >
>> >
>> >.
>> >
>
>.
>|||I wasn't 100% sure with this particular deadlock graph, so I took this
discussion offline with the othher MVPs & Ron Talmage was good enough to
provide this thorough analysis (below)
Regards,
Greg Linwood
SQL Server MVP
*********
Greg,
(a) SPID 63 is waiting for an S lock on the row in table 517576882 with a
clustered key hash value of e100d289ae1c.
(b) The deadlock is formed as follows:
1. SPID 84 has been granted an exclusive lock on the row in table 517576882
('the table'), clustered key (:1) with hash value e100d289ae1c.(Node 3)
SPID 84 is seeking to update the table's secondary index 15, hash value
1104e1b47512, to finish the update of the row (see in the Requested By
section of Node 1)
2. SPID 513 has been granted an S lock on the table's secondary index 15,
hash value 1104e1b47512. (Node 1)
SPID 84 is blocked by SPID 513, waiting for it to release its S lock on
secondary index 15.
3. SPID 63 is waiting to get an S lock on the same row of the table (Node 2)
SPID 63 is blocked by SPID 84, waiting for it to release its X lock on
the clustered key (see Requested By in Node 3)
4. SPID 513 cannot release its S lock on secondary index 15 until it gets an
S lock on the table's clustered key row.
SPID 513 requests the S lock but is put in a wait state behind SPID 63,
which is waiting on SPID 84.
SPID 513 is now blocked by SPID 63.
5. A deadlock cycle is detected and SPID 63 is chosen as the victim.
My guess is that another deadlock occurs almost immediately aftwerwards
between SPIDs 84 and 513, with SPID 513 as the victim, and it also is in the
error log.
Even though the statement type for SPIDs 63 and 513 are INSERT, the deadlock
arises with their requesting S locks on the table's row that is being
updated by SPID 84. Most probably those S lock requests are due to a SELECT
statement in the stored procedure GetSubscription, the procedure involved in
both 63 and 513. Assuming that GetSubscription is using the default
isolation level of READ COMMITTED, adding a NOLOCK or READPAST hint to the
SELECT statement against the table in GetSubscription could resolve the
deadlocks.
Without having the stored procedures and tables available, it's hard to be
more definite.
BTW, 15 or more indexes on a table is quite a lot. If index 15 isn't needed,
that would also resolve this partiicular deadlock!
Hope this helps,
Ron
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3c558$cdbe4aa0$a601280a@.phx.gbl...
> Can you tell me what you get from this and how so i can
> learn for the furtue:
> Deadlock encountered ... Printing deadlock information
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Wait-for graph
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:1
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
> (1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 0::
> 2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
> S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
> Value:0xef529e0 Cost:(0/84)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:2
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Wait List:
> 2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
> Value:0x7e27b780 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:3
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 3::
> 2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
> X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
> Statement Type: UPDATE Line #: 23
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> SetEventSentDate;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 End deadlock search
> 41725 ... a deadlock was found.
> 2003-12-17 03:55:49.99 spid4 --
> --
> Cheers :)
> >--Original Message--
> >Can you post the full deadlock graph? This is the output
> from trace 1204.
> >There is better information in other parts of that output
> that will help us
> >get closer to your problem faster.
> >
> >Trying to reverse back from page # to the cause of the
> deadlock is doing it
> >the hard way. The graph will reveal importantly, what
> type of dealock is
> >occurring & where. I'm on Australian time, so if you can
> do this within an
> >hour or so I'll look again tonight, otherwise hopefully
> someone else can
> >help too..
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> >> I have a deadlock reported (DBCC Trace on 1204) where
> >> the "lock" is this:
> >> PAG: 7:1:165015 and
> >> PAG: 7:1:183854
> >>
> >> How can i find out what "thing" is that. Ex:
> >> KEY 8:1653632984:2
> >> I can find the table and index associated with this
> >> number.. how can i do that for PAG: 7:1:183854
> >>
> >> very grateful for your help
> >>
> >> >--Original Message--
> >> >SQL Server stores tables, indexes etc internally
> >> on "pages", which are 8kb
> >> >disk structures in SQL 7 / 2000. Additionally, pages
> are
> >> allocated in
> >> >"Extents", which are groups of 8 pages (64kb).
> >> >
> >> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> >> database #7, file #
> >> >1 & page # 165015 in the internal storage system.
> >> >
> >> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> refers
> >> to database # 8,
> >> >Object # 1653632984 & Index #2. You can identify the
> >> index by looking up
> >> >that objectid in the sysobjects system table.. It's
> >> likely a table & the
> >> >index is the second index on that table.
> >> >
> >> >This type of information is generally displayed in
> >> locking analysis tools
> >> >such as deadlock graphs or output from sp_lock. Where
> are
> >> you seeing it? And
> >> >do you have a wider problem?
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >> >
> >> >
> >> >":)" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> >> Can anyone tell me what this is :
> >> >>
> >> >> PAG: 7:1:165015
> >> >>
> >> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >> >>
> >> >> Thanxs
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Reading deadlock graphs is not easy. Explaining it requires explaining quite
a deal about locking types, lock type compatabilities, lockable resources,
deadlock types etc. Each of these is a fairly substantial topic so I'd
encourage you to read up on these first before trying to work out deadlock
graphs.
One excellent resource is Kalen Delaney's "Inside SQL Server" which has a
whole chapter dedicated to locking and even a section in that chapter on
reading deadlock graphs. Kalen also has an e-book on the topic, available
here: http://tinyurl.com/puwn
Deadlocks can be cause by either cyclical resource acquisition or lock type
conversion. Read up on these in Books Online of Kalen's book first.
The nodes in the graph represent the contributing paths that form the
"deadly embrace" in a deadlock. Usually these will be from different
connections (spids) and it is far more common to see only two nodes in a
graph. Yours has 3 which puts it into the more unusual category. There can
be more but these are relatively rare.
Each node describes a locked resource (KEY: ... in your case) that a
particular connection has either acuired (grant list) or is waiting on (wait
list). There is also usually a Requested By section which shows which
connection is requesting the competing lock. All lock requests are displayed
with their lock types (eg X=Exclusive, S=Shard, IX=IntentExclusive etc, etc)
so that the reader can compare to the lock compatability matrix.
Additionally, the Input buffer is also displayed for the connection's last
statement in the node. In your case, you can see that the "GetSubscription"
and "SetEventSentDate" stored procedures are contributing to this deadlock
scenario. Two of the three connections were executing GetSubscription,
including spid 63, which was the one chosen as the deadlock victim. The
offending line number is also displayed in your case, allowing you to hone
in on the root of the problem.
The biggest problem you face is working out what to actually do next. This
cannot usually be established just by looking at these stored procs in
isolation. Usually a more holistic approach is required to reduce the
effects of deadlocking. It's also important to understand that deadlocking
cannot be 100% removed from SQL Server apps, so you need catch / re-try
logic in youur application. Often, this is the most effective means of
dealing with deadlocks - simple retry logic can be very effective.
Ron points out in his post that you appear to have 15 indexes on the table
involved in this deadlock. Perhaps analysing if this many indexes are really
needed is the best thing you can do.
To establish which database your deadlock is occurring in, run the following
query:
select name from master..sysdatabases where dbid = 7
I've chosen 7 because this is the DBID following the KEY: part in each node
of the deadlock graph.
Once you've got the database name, run the following query:
select object_name(517576882)
This shows the name of the table that the deadlock is occurring against.
Basically, your deadlock is occurring when an update occurs on that table.
First, the table's clustered index is updated, then a secondary index (index
#15) is updated. This is where the deadlock occurs. If you experience this
deadlock a lot, perhaps removing that index might help relieve the
situation.
Anyway, this response has become quite long-winded. I hope it's of some use
to you!
Regards,
Greg Linwood
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3c558$cdbe4aa0$a601280a@.phx.gbl...
> Can you tell me what you get from this and how so i can
> learn for the furtue:
> Deadlock encountered ... Printing deadlock information
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Wait-for graph
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:1
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:15
> (1104e1b47512) CleanCnt:1 Mode: S Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 0::
> 2003-12-17 03:55:49.99 spid4 Owner:0x35b61e00 Mode:
> S Flg:0x0 Ref:0 Life:00000001 SPID:513 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 513 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: X SPID:84 ECID:0 Ec:(0x0E741588)
> Value:0xef529e0 Cost:(0/84)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:2
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Wait List:
> 2003-12-17 03:55:49.99 spid4 Owner:0x3b4d7e80 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:63 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 63 ECID: 0
> Statement Type: INSERT Line #: 35
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> GetSubscription;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:513 ECID:0 Ec:(0x3E761588)
> Value:0x7e27b780 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 Node:3
> 2003-12-17 03:55:49.99 spid4 KEY: 7:517576882:1
> (e100d289ae1c) CleanCnt:2 Mode: X Flags: 0x0
> 2003-12-17 03:55:49.99 spid4 Grant List 3::
> 2003-12-17 03:55:49.99 spid4 Owner:0x3bb827e0 Mode:
> X Flg:0x0 Ref:0 Life:02000000 SPID:84 ECID:0
> 2003-12-17 03:55:49.99 spid4 SPID: 84 ECID: 0
> Statement Type: UPDATE Line #: 23
> 2003-12-17 03:55:49.99 spid4 Input Buf: RPC Event:
> SetEventSentDate;1
> 2003-12-17 03:55:49.99 spid4 Requested By:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4 Victim Resource Owner:
> 2003-12-17 03:55:49.99 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:63 ECID:0 Ec:(0x0A001588)
> Value:0x3b4d7e80 Cost:(0/0)
> 2003-12-17 03:55:49.99 spid4
> 2003-12-17 03:55:49.99 spid4 End deadlock search
> 41725 ... a deadlock was found.
> 2003-12-17 03:55:49.99 spid4 --
> --
> Cheers :)
> >--Original Message--
> >Can you post the full deadlock graph? This is the output
> from trace 1204.
> >There is better information in other parts of that output
> that will help us
> >get closer to your problem faster.
> >
> >Trying to reverse back from page # to the cause of the
> deadlock is doing it
> >the hard way. The graph will reveal importantly, what
> type of dealock is
> >occurring & where. I'm on Australian time, so if you can
> do this within an
> >hour or so I'll look again tonight, otherwise hopefully
> someone else can
> >help too..
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:055f01c3c553$54a988a0$a401280a@.phx.gbl...
> >> I have a deadlock reported (DBCC Trace on 1204) where
> >> the "lock" is this:
> >> PAG: 7:1:165015 and
> >> PAG: 7:1:183854
> >>
> >> How can i find out what "thing" is that. Ex:
> >> KEY 8:1653632984:2
> >> I can find the table and index associated with this
> >> number.. how can i do that for PAG: 7:1:183854
> >>
> >> very grateful for your help
> >>
> >> >--Original Message--
> >> >SQL Server stores tables, indexes etc internally
> >> on "pages", which are 8kb
> >> >disk structures in SQL 7 / 2000. Additionally, pages
> are
> >> allocated in
> >> >"Extents", which are groups of 8 pages (64kb).
> >> >
> >> >PAG: 7:1:165015 denotes {db_id}:{file_id}:{page_no}, so
> >> database #7, file #
> >> >1 & page # 165015 in the internal storage system.
> >> >
> >> >Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> refers
> >> to database # 8,
> >> >Object # 1653632984 & Index #2. You can identify the
> >> index by looking up
> >> >that objectid in the sysobjects system table.. It's
> >> likely a table & the
> >> >index is the second index on that table.
> >> >
> >> >This type of information is generally displayed in
> >> locking analysis tools
> >> >such as deadlock graphs or output from sp_lock. Where
> are
> >> you seeing it? And
> >> >do you have a wider problem?
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >> >
> >> >
> >> >":)" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:059e01c3c54c$68883850$a101280a@.phx.gbl...
> >> >> Can anyone tell me what this is :
> >> >>
> >> >> PAG: 7:1:165015
> >> >>
> >> >> Ex: KEY 8:1653632984:2 = (DBID, ObjectID, IndexID)
> >> >>
> >> >> Thanxs
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >