troubleshooting Question

SQL Locking Issue

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server 2008
4 Comments1 Solution100 ViewsLast Modified:
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
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros