Avatar of rwheeler23
rwheeler23
Flag for United States of America

asked on 

SQL Locking Issue

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
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
rwheeler23

8/22/2022 - Mon