title: How to find out what is locking my tables?
body: <p>I have a SQL table that all of a sudden cannot return data unless I include <code>with (nolock)</code> on the end, which indicates some kind of lock left on my table.  </p>
  
  <p>I've experimented a bit with <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017" rel="noreferrer">sys.dm_tran_locks</a> to identify that there are in fact a number of locks on the table, but how do I identify <em>what</em> is locking them (ie the request element of the <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017" rel="noreferrer">sys.dm_tran_locks</a>)?</p>
  
  <p>EDIT: I know about <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-2017" rel="noreferrer">sp_lock</a> for pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017" rel="noreferrer">sys.dm_tran_locks</a>.  I'm using SQL Server 2008 R2.</p>