How to check which locks are held on a table
database, performance, sql, sql-server, sql-server-2005
Solution
To add to the other responses, `sp_lock` can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with `sp_who2` to quickly zero in on locking problems.
There are multiple different versions of "friendlier" `sp_lock` procedures available online, depending on the version of SQL Server in question.
In your case, for SQL Server 2005, `sp_lock` is still available, but deprecated, so it's now recommended to use the `sys.dm_tran_locks` view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.
Problem
How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005