| Author |
Message |
ginacresse
Joined: 13 Oct 2011
Posts: 6
|
 Posted: Thu Oct 13, 2011 8:24 pm Post subject: Deadlock with no SQL Query Fragment |
 |
|
I have a deadlock alert that provides the following:
Object blocked: Index/(Key) 72057595237695488
Deadlocked processes
SPID Victim Lock details Statement type
139 dbid: 10, object id: 72057595237695488, index id: (92f44a2c605d)
239 Victim process dbid: 10, object id: 72057595237957632, index id: (d757d3151bfd)
SQL query fragment:
There is no query fragment displayed, and when I query the hobt_id (mislabeled object id according to a previous post) I get the same table with all indexes for both 72057595237695488 and 72057595237957632.
Where can I get more helpful information about this deadlock? The application that executes the sql actually e-mails me an error message with the stored procedure name and the error line number for the victim process, so I'm not sure why SQL Monitor didn't provide at least as much information. I'm probably just not looking in the right place. |
|
| Back to top |
|
 |
priyasinha
Joined: 03 Jan 2007
Posts: 483
|
| Posted: Fri Oct 14, 2011 9:20 am Post subject: |
|
|
Hi,
Hope this post answers your question.
As far as SQL query fragment is concerned, SQL Monitor is able to capture full query only when you are running trace. Please note that trace has performance impact on your monitored server and should only be used for short period of time when diagnosing a problem.
Thanks,
Priya |
|
| Back to top |
|
|
ginacresse
Joined: 13 Oct 2011
Posts: 6
|
| Posted: Sat Oct 15, 2011 1:13 am Post subject: |
|
|
Hi Priya,
When I run the sql from the post you directed me to:
| Code: |
SELECT
object_name(partitions.object_id) AS [Table],
indexes.name AS [Index]
FROM
sys.partitions AS partitions
INNER JOIN
sys.indexes AS indexes
ON partitions.object_id = indexes.object_id
WHERE
hobt_id = 72057595237695488
|
I get the following results:
Table Index
InventoryTran cidx_InventoryTran
InventoryTran PK_InventoryTran
InventoryTran idx_InventoryTran
InventoryTran idx_InventoryTran1
InventoryTran idx_InventoryTran2
InventoryTran idx_InventoryTran3
InventoryTran idx_TranDate
InventoryTran idx_InventoryTran4
InventoryTran idx_InventoryTran5
InventoryTran idx_InventoryTran6
InventoryTran idx_InventoryTran7
InventoryTran idx_InventoryTran8
InventoryTran idx_InventoryTran9
When I run the same code with the victim hbid_id:
| Code: |
SELECT
object_name(partitions.object_id) AS [Table],
indexes.name AS [Index]
FROM
sys.partitions AS partitions
INNER JOIN
sys.indexes AS indexes
ON partitions.object_id = indexes.object_id
WHERE
hobt_id = 72057595237957632
|
I get identical results:Table Index
InventoryTran cidx_InventoryTran
InventoryTran PK_InventoryTran
InventoryTran idx_InventoryTran
InventoryTran idx_InventoryTran1
InventoryTran idx_InventoryTran2
InventoryTran idx_InventoryTran3
InventoryTran idx_TranDate
InventoryTran idx_InventoryTran4
InventoryTran idx_InventoryTran5
InventoryTran idx_InventoryTran6
InventoryTran idx_InventoryTran7
InventoryTran idx_InventoryTran8
InventoryTran idx_InventoryTran9
I'm not sure how this helps me. Am I missing something? |
|
| Back to top |
|
|
priyasinha
Joined: 03 Jan 2007
Posts: 483
|
| Posted: Mon Oct 17, 2011 9:43 am Post subject: |
|
|
Hi,
Apologies but I have to look/ read myself first. I have never done deadlock decoding so won't be able to answer this quickly. SQL Monitor pulls this information directly from SQL Server and presents it on Alert Details. It doesn't manipulate the information in anyway.
I will post my findings here after I have looked at this.
Thanks,
Priya |
|
| Back to top |
|
|
|
|
All times are GMT + 1 Hour
|
| Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
