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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author 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ãoMSSQL Senior EngineerCommented:
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.
rwheeler23Author Commented:
Good revision. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.