How to find out what is locking my tables?

sql sql-server sql-server-2008-r2 locking query-optimization
just.another.programmer picture just.another.programmer · Jan 5, 2012 · Viewed 327.2k times · Source

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

Nicholas Carey picture Nicholas Carey · Jan 5, 2012

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:

enter image description here

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.

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More