
Be sure to get the latest version of this script from our GitHub, https://github.com/SQLUndercover/UndercoverToolbox
Have you ever wanted a quick and easy way to see who was holding (and waiting on) locks on a particular database? Perhaps you’ve got some blocking issues going on and you want to see exactly which rows the row level locks were taken out on?
sp_LockDetails will return some handy information about the locks held on a specific database, including SPID, login, database, lock type and resource.
Just run it from the context of the database that you’re interested in.
Running it with @DetailedView set to 1 will also return the rows associated with any KEY or RID locks. Note that this performs a dirty read, so any rows that are returned will be in a state after any modification was performed on them,
Parameters
@DetailedView – Returns the associated rows for KEY and RID locks. (DEFAULT = 0)
/****************************************************************** Author: David Fowler Revision date: 25/09/2019 Version: 1 © www.sqlundercover.com This script is for personal, educational, and internal corporate purposes, provided that this header is preserved. Redistribution or sale of this script,in whole or in part, is prohibited without the author's express written consent. The software is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. in no event shall the authors or copyright holders be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the software. ******************************************************************/ USE master GO IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'sp_LockDetails') DROP PROC sp_LockDetails GO CREATE PROC sp_LockDetails (@DetailedView BIT = 0) AS BEGIN IF OBJECT_ID('tempdb.dbo.#LockInfo') IS NOT NULL DROP TABLE #LockInfo --create table to stage locking data CREATE TABLE #LockInfo( request_session_id int NOT NULL ,DatabaseName nvarchar(128) NULL ,login_name nvarchar(128) NOT NULL ,status nvarchar(30) NOT NULL ,request_mode nvarchar(60) NOT NULL ,request_status nvarchar(60) NOT NULL ,resource_type nvarchar(60) NOT NULL ,resource_description nvarchar(256) NOT NULL ,ObjectName nvarchar(128) NULL) --populate #lockinfo INSERT INTO #LockInfo SELECT tran_locks.request_session_id, DB_NAME(tran_locks.resource_database_id) AS DatabaseName, sessions.login_name, sessions.status, tran_locks.request_mode, tran_locks.request_status, tran_locks.resource_type, tran_locks.resource_description, COALESCE(objects.name, OBJECT_NAME(partitions.object_id),DB_NAME(tran_locks.resource_database_id)) AS ObjectName FROM sys.dm_tran_locks tran_locks JOIN sys.dm_exec_sessions sessions ON sessions.session_id = tran_locks.request_session_id LEFT OUTER JOIN sys.objects objects ON tran_locks.resource_associated_entity_id = objects.object_id LEFT OUTER JOIN sys.partitions partitions ON tran_locks.resource_associated_entity_id = partitions.hobt_id WHERE(resource_database_id) = DB_ID() --return locking details SELECT request_session_id ,DatabaseName ,login_name ,status ,request_mode ,request_status ,resource_type ,resource_description ,ObjectName FROM #LockInfo ORDER BY request_session_id, ObjectName IF @DetailedView = 1 BEGIN --if detailed view, create cursor to query all locked rows DECLARE KeyLockRows CURSOR STATIC FORWARD_ONLY FOR SELECT DISTINCT 'SELECT ''' + CAST(request_session_id AS NVARCHAR) +''' AS SPID, ''' + ObjectName + ''' AS ObjectName, + ''' + login_name + ''' AS LoginName, ''' + request_mode + ''' AS LockType, ''' + request_status + ''' AS status, * FROM ' + QUOTENAME(ObjectName) + ' WITH (NOLOCK) WHERE %%lockres%% IN (' + STUFF((SELECT ''',''' + RTRIM(resource_description) FROM #LockInfo locksXML WHERE locksXML.request_session_id = Locks.request_session_id AND locksXML.request_status = Locks.request_status AND locksXML.ObjectName = Locks.ObjectName AND resource_type IN ('KEY','RID') FOR XML PATH ('')),1,2,'') + ''')' FROM #LockInfo Locks WHERE resource_type IN ('KEY','RID') --GROUP BY ObjectName, login_name, request_status, request_session_id, request_mode OPEN KeyLockRows DECLARE @cmd NVARCHAR(4000) FETCH NEXT FROM KeyLockRows INTO @cmd --cursor through, returning all locked rows WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_executesql @cmd FETCH NEXT FROM KeyLockRows INTO @cmd END CLOSE KeyLockRows DEALLOCATE KeyLockRows END END GO EXEC sys.sp_MS_marksystemobject sp_LockDetails GO
Leave a Reply