sql - How to find out what is locking my tables?

Question

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table.

I've experimented a bit with sys.dm_tran_locks to identify that there are in fact a number of locks on the table, but how do I identify what is locking them (ie the request element of the sys.dm_tran_locks)?

EDIT: I know about sp_lock for pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with sys.dm_tran_locks. I'm using SQL Server 2008 R2.

Answer 1

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.