rwheeler23
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think the application is not closing connections so that could be an orphaned connection that went timed out from the application but still running in SQL Server side and when you closed the user's application it just killed all opened connections.
ASKER
Good revision. Thanks.
ASKER