SQL Server – Identifying Locks

--Stored procedure to display current locks/process

exec​​ sp_lock

--Stored procedure to display current activity/process

exec​​ sp_who2

--DMV to view locking information

SELECT​​ *​​ FROM​​ sys.dm_tran_locks

GO

--DMV to view blocked transactions 

SELECT​​ *​​ FROM​​ sys.dm_exec_requests​​ WHERE​​ status​​ =​​ 'suspended'

GO

--Trace flag to log deadlocks

DBCC​​ TRACEON​​ (1222,​​ -1)

GO