Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag 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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rwheeler23

ASKER

The application was continuously reporting a lock while I was running these scripts. Also, other users were also showing the lock so it was in place while I ran these scripts. What makes this even stranger was I started to walk around to each workstation to see if anyone else could have been on this record and I found none. Finally what released the lock was to exit each user out of the entire application. The one that did the trick was someone sitting at the main menu and not even on a potential screen to cause this lock. I will try your suggestion.
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.
Good revision. Thanks.