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

• 
  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/3540 ... -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 )
  

  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 May 11, 2011 08:40

  - 0 +
• 
  I got it to work. I had to join in sys.indexes to get index name:
  

  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 May 11, 2011 13:54

  - 0 +
• 
  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 May 12, 2011 06:25

  - 0 +

Add comment

Please sign in to leave a comment.