I created a view to check on what objects are have active locks and which may or may not be blocking other session(s) because I wasn't fond of the Activity Monitor's Process listing:
if object_id( 'v_locks' ) is not null
drop view v_locks
go
create view v_locks as (
select status = sessns.status + ' (BLOCKED)',
locking = object_name( locks.resource_associated_entity_id)
+ ' (' +
case locks.request_mode when 'S' then 'Shared'
when 'U' then 'Update'
when 'X' then 'Exclusive'
when 'IS' then 'Intent Shared'
when 'IU' then 'Intent Update'
when 'IX' then 'Intent Exclusive'
when 'SIU' then 'Shared Intent Update'
when 'SIX' then 'Shared Intent Exclusive'
when 'UIX' then 'Update Intent Exclusive'
when 'BU' then 'Bulk Update'
else 'Other'
end + ')',
computer = sessns.host_name,
login = sessns.nt_domain + '\' + sessns.nt_user_name,
program = sessns.program_name,
rqst.session_id,
pending_command = rqst.command,
blocked_by = convert(varchar,rqst.blocking_session_id),
rqst.wait_type,
isolation_level = case rqst.transaction_isolation_level
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable Read'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end,
last_sql = ( select TEXT from sys.dm_exec_sql_text( sql_handle ) )
from sys.dm_exec_requests rqst,
sys.dm_exec_sessions sessns,
sys.dm_tran_locks locks
where rqst.blocking_session_id <> 0
and rqst.session_id = sessns.session_id
and rqst.session_id = locks.request_session_id
and locks.resource_type in ('OBJECT')
union
select status = sessns.status + ' (BLOCKING)',
locking = object_name( locks.resource_associated_entity_id)
+ ' (' +
case locks.request_mode when 'S' then 'Shared'
when 'U' then 'Update'
when 'X' then 'Exclusive'
when 'IS' then 'Intent Shared'
when 'IU' then 'Intent Update'
when 'IX' then 'Intent Exclusive'
when 'SIU' then 'Shared Intent Update'
when 'SIX' then 'Shared Intent Exclusive'
when 'UIX' then 'Update Intent Exclusive'
when 'BU' then 'Bulk Update'
else 'Other'
end + ')',
computer = sessns.host_name,
login = sessns.nt_domain + '\' + sessns.nt_user_name,
program = sessns.program_name,
sessns.session_id,
pending_command = '',
blocked_by = '',
'',
isolation_level = case sessns.transaction_isolation_level
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable Read'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end,
last_sql = (select text from sys.dm_exec_sql_text( cons.most_recent_sql_handle ) )
from sys.dm_tran_locks locks
inner join sys.dm_exec_sessions sessns
on ( locks.request_session_id = sessns.session_id )
inner join sys.dm_exec_connections cons
on ( locks.request_session_id = cons.session_id )
where locks.resource_type in ('OBJECT')
and locks.request_session_id not in ( select session_id from sys.dm_exec_requests )
and locks.request_session_id in ( select blocking_session_id from sys.dm_exec_requests )
union
select status = sessns.status + ' (NOT BLOCKING)',
locking = object_name( locks.resource_associated_entity_id)
+ ' (' +
case locks.request_mode when 'S' then 'Shared'
when 'U' then 'Update'
when 'X' then 'Exclusive'
when 'IS' then 'Intent Shared'
when 'IU' then 'Intent Update'
when 'IX' then 'Intent Exclusive'
when 'SIU' then 'Shared Intent Update'
when 'SIX' then 'Shared Intent Exclusive'
when 'UIX' then 'Update Intent Exclusive'
when 'BU' then 'Bulk Update'
else 'Other'
end + ')',
computer = sessns.host_name,
login = sessns.nt_domain + '\' + sessns.nt_user_name,
program = sessns.program_name,
sessns.session_id,
pending_command = '',
blocked_by = '',
'',
isolation_level = case sessns.transaction_isolation_level
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable Read'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end,
last_sql = (select text from sys.dm_exec_sql_text( cons.most_recent_sql_handle ) )
from sys.dm_tran_locks locks
inner join sys.dm_exec_sessions sessns
on ( locks.request_session_id = sessns.session_id )
inner join sys.dm_exec_connections cons
on ( locks.request_session_id = cons.session_id )
where locks.resource_type in ('OBJECT')
and locks.request_session_id not in ( select blocking_session_id from sys.dm_exec_requests )
and locks.request_session_id not in ( select session_id from sys.dm_exec_requests )
);
select * from v_locks;
