Waitresource - Find Exact Key

  • Ekta-246552

    SSC Veteran

    Points: 249

    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!

  • This was removed by the editor as SPAM

  • Jo Pattyn

    SSC-Dedicated

    Points: 31373

    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

    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

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

  • kurt.yang

    SSC Veteran

    Points: 291

    please refer

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

    there is a detailed explaination for every kind of waitresrouce.

  • Avijeet

    Old Hand

    Points: 381

    Thanks it was really helpful information

  • james.stead

    Valued Member

    Points: 61

    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: 33287

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

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

    Thanks.

  • Alexander Suprun

    SSCertifiable

    Points: 6223

    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

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply