How to check which locks are held on a table

sql sql-server database performance sql-server-2005
usman shaheen picture usman shaheen · Mar 29, 2009 · Viewed 694.7k times · Source

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

Answer

mwigdahl picture mwigdahl · Mar 29, 2009

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.

Related questions

Favourite performance tuning tricks

When you have a query or stored procedure that needs performance tuning, what are some of the first things you try?

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More