I have used these scripts to routinely track down SQL locks. Recently I had two occasions where an application reported locks on records in a table however these routines indicated there were no locks. I even had one strange occurrence where after deleting the user's temp folder the SQL lock went away. Can anyone improve upon these scripts or suggest other SQL tools to improve the identification of SQL locks? This is for SQL 2008 R2 and above.
select * from sys.dm_exec_requests
where blocking_session_id <> 0 -- Session_id is the session that is locked and block_sssion_id is the session doing the blocking
select * from sys.sysprocesses
where spid = (select blocking_session_id from sys.dm_exec_requests
where blocking_session_id <> 0)
DBCC INPUTBUFFER(53) will give you the SQL statement being run.
select * from sys.dm_tran_locks