Waitresource - Find Exact Key

• Ekta-246552

  SSC Veteran

  Points: 249

  More actions

  February 16, 2007 at 7:08 am

  #176072

  I am trying to interpret deadlock trace information. This is what I see in the waitresource

  waitresource=KEY: 9:72057594065256448 (f20077ea2259)

  Can someone help me decipher this?

  Thanks!

• February 19, 2007 at 12:00 pm

  This was removed by the editor as SPAM

• Jo Pattyn

  SSC-Dedicated

  Points: 32405

  More actions

  February 20, 2007 at 12:38 am

  #690579

  Hello,

  There is a page about locking in sql server 7,2000 where the key is described.

  See http://support.microsoft.com/kb/224453/

• JK-211484

  Right there with Babe

  Points: 745

  More actions

  December 1, 2008 at 4:55 pm

  #905984

  That might be a hobt_id. You find it through sys.partitions:

  SELECT o.name, i.name

  FROM sys.partitions p

  JOIN sys.objects o ON p.object_id = o.object_id

  JOIN sys.indexes i ON p.object_id = i.object_id

  AND p.index_id = i.index_id

  WHERE p.hobt_id = 72057594065256448

• egoddard

  SSC Rookie

  Points: 43

  More actions

  April 4, 2011 at 10:06 am

  #1307063

  sys.partitions was introduced in SQL Server 2005, does anyone know the sql server 2000 equivalent?

• kurt.yang

  SSC Veteran

  Points: 291

  More actions

  October 17, 2011 at 12:18 am

  #1395142

  please refer

  http://support.microsoft.com/kb/224453/

  there is a detailed explaination for every kind of waitresrouce.

• Avijeet

  Old Hand

  Points: 381

  More actions

  February 14, 2012 at 5:05 am

  #1446769

  Thanks it was really helpful information

• james.stead

  Valued Member

  Points: 61

  More actions

  August 16, 2013 at 9:38 am

  #1642226

  I stumbled across this post looking for an answer. The link here says it is not possible to get back to the original row but that is not always true. With a low probability the below will return multiple rows because it uses a hash but typically I have found only one match.

  declare @databaseName varchar(100) = 'Your Database'--DatabaseName

  declare @keyValue varchar(100) = 'KEY: 10:72057600953614336 (0d0120c75d83)'--Output from deadlock graph

  declare @lockres varchar(100)

  declare @hobbitID bigint

  select @hobbitID = convert(bigint,RTRIM(SUBSTRING(@keyValue,CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)+1,

  CHARINDEX('(',@keyValue)-CHARINDEX(':',@keyValue,CHARINDEX(':',@keyValue)+1)-1)))

  select @lockRes = RTRIM(SUBSTRING(@keyValue,CHARINDEX('(',@keyValue)+1,CHARINDEX(')',@keyValue)-CHARINDEX('(',@keyValue)-1))

  declare @objectName sysname

  declare @ObjectLookupSQL as nvarchar(max) = '

  SELECT @objectName = o.name

  FROM '+@databaseName+'.sys.partitions p

  JOIN '+@databaseName+'.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]

  join '+@databaseName+'.sys.objects o on o.object_id = i.object_id

  WHERE hobt_id = '+convert(nvarchar(50),@hobbitID)+'

  '

  exec sp_executesql @ObjectLookupSQL, N'@objectName sysname OUTPUT',@objectName=@objectName OUTPUT

  select @objectName

  declare @finalResult nvarchar(max) = N'select %%lockres%% ,*

  from '+@databaseName+'.dbo.' + @objectName + '

  where %%lockres%% = ''('+@lockRes+')''

  '

  exec sp_executesql @finalResult

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  January 18, 2016 at 11:40 am

  #1852557

  How to search this key value (@keyValue) in the code?

  APPLICATION: 7:0:[TMsgRefs1]:(8886087a)

  Thanks.

• Alexander Suprun

  SSCertifiable

  Points: 6253

  More actions

  January 22, 2016 at 11:38 am

  #1853888

  SQL-DBA-01 (1/18/2016)

  ---

  How to search this key value (@keyValue) in the code?

  APPLICATION: 7:0:[TMsgRefs1]:(8886087a)

  Where do you see a "KEY"?

  It's an - APPLICATION lock. Caused by sp_getapplock procedure in DB_ID = 7 and Resource = 'TMsgRefs1'

  ---

  Alex Suprun