|
1 |
originally posted 2014-06-05 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
create procedure [dbo].[spGetLockedSqlObjects] as begin select [SessionId] = [lock].[request_session_id] ,[LockedObjectName] = [object].[Name] ,[Duration] = datediff(second,activetrans.[transaction_begin_time], getdate()) ,[TransactionBeginTime] = activetrans.[transaction_begin_time] ,[Locks] = count(*) from sys.dm_tran_locks [lock] with (nolock) join sys.partitions [partition] with (nolock) on [partition].[hobt_id] = [lock].[resource_associated_entity_id] join sys.objects [object] with (nolock) on [object].[object_id] = [partition].[object_id] join sys.dm_exec_sessions execsession with (nolock) on execsession.[session_id] = [lock].[request_session_id] join sys.dm_tran_session_transactions transession with (nolock) on transession.[session_id] = execsession.[session_id] join sys.dm_tran_active_transactions activetrans with (nolock) on activetrans.[transaction_id] = transession.[transaction_id] where [resource_database_id] = db_id() and [object].[type] = 'U' group by activetrans.[transaction_begin_time] ,[lock].[request_session_id] ,[object].[name] end |