SQL Locking Issue

rwheeler23
rwheeler23 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
Commented:
Recently I had two occasions where an application reported locks on records in a table however these routines indicated there were no locks
How long did you take from the application lock report until you ran the script? The lock may gone away during that small period you took to react.

The 2nd statement can be improved:
select p.* 
from sys.sysprocesses p
    inner join sys.dm_exec_requests r on p.spid = r.blocking_session_id

Open in new window

rwheeler23President

Author

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
rwheeler23President

Author

Commented:
Good revision. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial