Red Gate forums :: View topic - Deadlock with no SQL Query Fragment
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 2
SQL Monitor 2 forum

Deadlock with no SQL Query Fragment

Search in SQL Monitor 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ginacresse



Joined: 13 Oct 2011
Posts: 6

PostPosted: Thu Oct 13, 2011 8:24 pm    Post subject: Deadlock with no SQL Query Fragment Reply with quote

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
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Fri Oct 14, 2011 9:20 am    Post subject: Reply with quote

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
View user's profile Send private message
ginacresse



Joined: 13 Oct 2011
Posts: 6

PostPosted: Sat Oct 15, 2011 1:13 am    Post subject: Reply with quote

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
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Mon Oct 17, 2011 9:43 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Reply to topic 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