Red Gate forums :: View topic - How do I get a blocked index name for a deadlock?
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

How do I get a blocked index name for a deadlock?

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



Joined: 12 Jun 2006
Posts: 17
Location: Sacramento CA

PostPosted: Tue May 10, 2011 4:26 pm    Post subject: How do I get a blocked index name for a deadlock? Reply with quote

Example:

Object blocked: Index/(Key) 72057618332712960

Process detail:
dbid: 8, object id: 72057618332712960, index id: (010086470766)


Both of these numbers are too long to be the object_id (INT) in sys.all_objects
_________________
Keith Walton
Chief Software Architect
NHXS
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Wed May 11, 2011 1:40 pm    Post subject: Reply with quote

Hi Keith

We get this value directly from the SQL Server error log. I've investigated why the value is so long and the best explanation I've come up with so far is here:

http://stackoverflow.com/questions/354035/sql-server-deadlock-object-ids-are-too-large

This seems to suggest that the values are hobt rather than object ids.

It might be worth doing as that page suggests and run the following inside the relevant database (id = 8 )

Code:
SELECT hobt_id, object_name(p.[object_id]), index_id
FROM sys.partitions p
WHERE hobt_id = 72057618332712960


This isn't working for me at the moment, but it'd be interesting to see if it works for you.

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
Back to top
View user's profile Send private message
Keith_Walton



Joined: 12 Jun 2006
Posts: 17
Location: Sacramento CA

PostPosted: Wed May 11, 2011 6:54 pm    Post subject: Reply with quote

I got it to work. I had to join in sys.indexes to get index name:

Code:
SELECT name FROM master..sysdatabases WHERE dbid = 8

USE support
GO


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               = 72057618332712960



It would be cool if SQL Monitor would do this automatically.

Thanks
_________________
Keith Walton
Chief Software Architect
NHXS
Back to top
View user's profile Send private message
Chris Spencer



Joined: 29 Aug 2006
Posts: 301
Location: Red Gate - Cambridge

PostPosted: Thu May 12, 2011 11:25 am    Post subject: Reply with quote

Hi Keith

Yes that seems to do the trick!!

As deadlock alerting is a very important part of SQL Monitor, I've raised a few enhancements requests and bug reports.

SRP-4174 : Enhancement that you have requested here to get the values auto-decoded into something more meaningful.

SRP-4175 : Bug report as we are misleading users by suggesting that some value is an object id when it is in fact a hobt id.

SRP-4176 : A more comprehensive enhancement request suggesting that we move to using trace flag 1222 rather than 1204. (1204 is required for SQL Server 2000 support but I can imagine it's possible to use one or the other when appropriate). 1222 gives far better information.

Regards
Chris
_________________
Chris Spencer
Test Engineer
Red Gate
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