and object = BIGINT = SYSNAME = VARCHAR(255) = '(543066506c7c)' ĭECLARE NVARCHAR(MAX) = + N'.sys.sp_executesql' ĭECLARE NVARCHAR(MAX) = N'SELECT %%LOCKRES%%,*įROM MySchema.MyTable WHERE %%LOCKRES%% = VARCHAR(255)', can do this without dynamic SQL, of course, but this gives you a nice template for a snippet or stored procedure where you can just plug the values in, if this is something you troubleshoot a lot. The 543066506c7c is essentially a hash of the primary key, and you can retrieve that row (and potentially any rows with a hash collision) using this dynamic SQL: - Given: KEY: 9:72057632651542528 (543066506c7c) Adding as community wiki only because it's a slightly different approach and adds a bit of other info. Sorry, was already working on this answer and about to post when the other appeared. print sp_executesql refer to this excellent blog post on : The Curious Case of the Dubious Deadlock and the Not So Logical Lock print nvarchar(max) = N'select %%lockres%% ,* WHERE hobt_id = ' + convert(nvarchar(50), + '' Join ' + '.sys.objects o on o.object_id = i.object_id Microsoft has still left %%lockres%% as undocumented feature.īelow is the script that will help you : declare varchar(100) = 'yourdatabaseName' -CHANGE HERE !ĭeclare varchar(100) = 'KEY: 9:72057632651542528 (543066506c7c)' -Output from deadlock graph - CHANGE HERE ! + ' WHERE %%lockres%% = sp_executesql nvarchar(255)', = is a supplement to the answers already posted by DBAFromTheCold and Aaron Bertrand. NOTE: we use the NOLOCK hint to avoid locking the table when searching by %%lockres%%, which might generate table scans.ĭECLARE nvarchar(max) = N'SELECT lockResKey = %%lockres%% ,*įROM ' + ISNULL(' WITH(NOLOCK INDEX(' + + ')) ', '') RAISERROR(N'The object "%s" was not the row that was blocked JOIN ' + '.sys.objects o on o.object_id = i.object_idĮxec sp_executesql int, bigint, sysname OUTPUT, sysname OUTPUT, sysname = outputĭECLARE nvarchar(512) = + '.' + + '.' + fullObjectName = lockIndex = lockRes_key = hobt_id = waitresource_keyValue = object name prior to running dynamic SQL JOIN ' + + '.sys.indexes i ON p.index_id = i.index_id AND p. ![]() SELECT = o.name, = i.name, = OBJECT_SCHEMA_NAME(p.object_id, ' + + '.sys.partitions p SELECT = not exists(select * from sys.databases d where d.name = %s was not found.', 16, 1, sysname, sysname, sysname Validate DB name prior to running dynamic SQL ![]() Should not have to change anything below this line:ĭeclare nvarchar(255), bigint, int, sysname ![]() WHERE %%lockres%% = is an example script modified using pieces from each of these answers. WHERE %%lockres%% = if you add the index query hint, it does return the correct resultsįROM. This index is not always the PK or clustered index.įor example: -Sometimes this does not return the correct results. One important piece of info I found during testing that the other answers left out is that you need to use the index that is returned from sys.partitions for the given HOBT_ID when looking up the %%lockres%% (via an index query hint). The answers from and are great and were very helpful.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |