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

User · Answer

You can also use sp who2 which gives more information   Here is some info http   dbadiaries com using-sp who2-to-help-with-sql-server-troubleshooting

User · Answer

I have a stored procedure that I have put together  that deals not only with locks and blocking  but also to see what is running in a server  I have put it in master  I will share it with you  the code is below   USE  master  go   CREATE PROCEDURE  dbo   sp radhe    AS BEGIN  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   -- the current processes -- marcelo miorelli  -- CCHQ  -- 04 MAR 2013 Wednesday  SELECT es session id AS session id  COALESCE es original login name      AS login name  COALESCE es host name     AS hostname  COALESCE es last request end time es last request start time  AS last batch  es status  COALESCE er blocking session id 0  AS blocked by  COALESCE er wait type  MISCELLANEOUS   AS waittype  COALESCE er wait time 0  AS waittime  COALESCE er last wait type  MISCELLANEOUS   AS lastwaittype  COALESCE er wait resource     AS waitresource  coalesce db name er database id   No Info   as dbid  COALESCE er command  AWAITING COMMAND   AS cmd  sql text st text  transaction isolation   CASE es transaction isolation level     WHEN 0 THEN  Unspecified      WHEN 1 THEN  Read Uncommitted      WHEN 2 THEN  Read Committed      WHEN 3 THEN  Repeatable      WHEN 4 THEN  Serializable      WHEN 5 THEN  Snapshot  END  COALESCE es cpu time 0         COALESCE er cpu time 0  AS cpu  COALESCE es reads 0         COALESCE es writes 0         COALESCE er reads 0     COALESCE er writes 0  AS physical io  COALESCE er open transaction count -1  AS open tran  COALESCE es program name     AS program name  es login time FROM sys dm exec sessions es LEFT OUTER JOIN sys dm exec connections ec ON es session id   ec session id LEFT OUTER JOIN sys dm exec requests er ON es session id   er session id LEFT OUTER JOIN sys server principals sp ON es security id   sp sid LEFT OUTER JOIN sys dm os tasks ota ON es session id   ota session id LEFT OUTER JOIN sys dm os threads oth ON ota worker address   oth worker address CROSS APPLY sys dm exec sql text er sql handle  AS st where es is user process   1    and es session id  lt  gt    spid   and es status    running  ORDER BY es session id  end   GO   this procedure has done very good for me in the last couple of years  to run it just type sp radhe  Regarding putting sp radhe in the master database  I use the following code and make it a system stored procedure  exec sys sp MS marksystemobject  sp radhe    as you can see on the link below  Creating Your Own SQL Server System Stored Procedures  Regarding the transaction isolation level  Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask  Jonathan Kehayias     Once you change the transaction isolation level it only changes when   the scope exits at the end of the procedure or a return call  or if   you change it explicitly again using SET TRANSACTION ISOLATION LEVEL       In addition the TRANSACTION ISOLATION LEVEL is only scoped to the   stored procedure  so you can have multiple nested stored procedures   that execute at their own specific isolation levels

User · Answer

As per the official docs the sp lock is mark as deprecated      This feature is in maintenance mode and may be removed in a future   version of Microsoft SQL Server  Avoid using this feature in new   development work  and plan to modify applications that currently use   this feature    and it is recommended to use sys dm tran locks instead  This dynamic management object returns information about currently active lock manager resources  Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted   It generally returns more details in more user friendly syntax then sp lock does   The whoisactive routine written by Adam Machanic is very good to check the current activity in your environment and see what types of waits locks are slowing your queries  You can very easily find what is blocking your queries and tons of other handy information     For example  let s say we have the following queries running in the default SQL Server Isolation Level - Read Committed  Each query is executing in separate query window   -- creating sample data CREATE TABLE  dbo   DataSource         RowID  INT PRIMARY KEY      RowValue  VARCHAR 12      INSERT INTO  dbo   DataSource   RowID    RowValue   VALUES  1    samle data     -- query window 1 BEGIN TRANSACTION       UPDATE  dbo   DataSource      SET  RowValue     new data      WHERE  RowID    1   --COMMIT TRANSACTION   -- query window 2 SELECT   FROM  dbo   DataSource     Then execute the sp whoisactive  only part of the columns are displayed      You can easily seen the session which is blocking the SELECT statement and even its T-SQL code  The routine has a lot of parameters  so you can check the docs for more details   If we query the sys dm tran locks view we can see that one of the session is waiting for a share lock of a resource  that has exclusive lock by other session

User · Answer

exec sp lock   This query should give you existing locks     exec sp who SPID -- will give you some info   Having spids  you could check activity monitor processes tab  to find out what processes are locking the tables   details  for more info and  kill process  to kill it

User · Answer

This should give you all the details of the existing locks   DECLARE  tblVariable TABLE SPID INT  Status VARCHAR 200    Login  VARCHAR 200   HostName VARCHAR 200        BlkBy VARCHAR 200   DBName VARCHAR 200   Command VARCHAR 200   CPUTime INT       DiskIO INT  LastBatch VARCHAR 200   ProgramName VARCHAR 200    SPID INT       RequestID INT   INSERT INTO  tblVariable EXEC Master dbo sp who2  SELECT v    t TEXT  FROM  tblVariable v INNER JOIN sys sysprocesses sp ON sp spid   v SPID CROSS APPLY sys dm exec sql text sp sql handle  AS t ORDER BY BlkBy DESC  CPUTime DESC   You can then kill  with caution  the SPID that blocks your table   kill 104 -- Your SPID

User · Answer

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

User · Answer

A colleague and I have created a tool just for this  It s a visual representation of all the locks that your sessions produce  Give it a try  http   www sqllockfinder com   it s open source  https   github com LucBos SqlLockFinder

User · Answer

For getting straight to  who is blocked blocking  I combined abbreviated sp who and sp lock into a single query which gives a nice overview of who has what object locked to what level   --Create Procedure WhoLock --AS set nocount on if object id  tempdb   locksummary   is not null Drop table  locksummary if object id  tempdb   lock   is not null Drop table  lock create table  lock      spid int     dbid int     objId int     indId int     Type char 4      resource nchar 32      Mode char 8      status char 6   Insert into  lock exec sp lock if object id  tempdb   who   is not null Drop table  who create table  who       spid int  ecid int  status char 30               loginame char 128   hostname char 128               blk char 5   dbname char 128   cmd char 16              --               request id INT --Needed for SQL 2008 onwards             --            Insert into  who exec sp who Print  -----------------------------------------  Print  Lock Summary for       servername       excluding tempdb    Print  -----------------------------------------    Char 10  Select     left loginame  28  as loginame       left db name dbid  128  as DB      left object name objID  30  as object      max mode  as  ToLevel       Count    as  How Many       Max Case When mode   X  Then cmd Else null End  as  Xclusive lock for command       l spid  hostname into  LockSummary from  lock l join  who w on l spid  w spid where dbID    db id  tempdb   and l status  GRANT  group by dbID  objID  l spid  hostname  loginame  Select   from  LockSummary order by  ToLevel  Desc   How Many  Desc  loginame  DB  object  Print  --------  Print  Who is blocking   Print  --------    char 10  SELECT p spid  convert char 12   d name  db name   program name   p loginame   convert char 12   hostname  hostname   cmd   p status   p blocked   login time   last batch   p spid FROM      master  sysprocesses p JOIN      master  sysdatabases d ON p dbid    d dbid WHERE     EXISTS    SELECT 1           FROM      master  sysprocesses p2           WHERE     p2 blocked   p spid    Print  --------  Print  Details   Print  --------    char 10  Select     left loginame  30  as loginame   l spid      left db name dbid  15  as DB      left object name objID  40  as object      mode       blk      l status from  lock l join  who w on l spid  w spid where dbID    db id  tempdb   and blk  lt  gt 0 Order by mode desc  blk  loginame  dbID  objID  l status    For what the lock level abbreviations mean  see e g  https   technet microsoft com en-us library ms175519 28v sql 105 29 aspx   Copied from  sp WhoLock     a T-SQL stored proc combining sp who and sp lock     NB the  Xclusive lock for command  column can be misleading -- it shows the current command for that spid  but the X lock could have been triggered by an earlier command in the transaction

User · Answer

Plot twist   You can have orphaned distributed transactions holding exclusive locks and you will not see them if your script assumes there is a session associated with the transaction  there isn t    Run the script below to identify these transactions    WITH ORPHANED TRAN AS   SELECT     dat name      dat transaction uow      ddt database transaction begin time      ddt database transaction log bytes reserved      ddt database transaction log bytes used FROM     sys dm tran database transactions ddt      sys dm tran active transactions dat      sys dm tran locks dtl WHERE     ddt transaction id   dat transaction id AND     dat transaction id   dtl request owner id AND     dtl request session id   -2 AND     dtl request mode    X    SELECT DISTINCT   FROM ORPHANED TRAN   Once you have identified the transaction  use the transaction uow column to find it in MSDTC and decide whether to abort or commit it  If the transaction is marked as In Doubt  with a question mark next to it  you will probably want to abort it   You can also kill the Unit Of Work  UOW  by specifying the transaction uow in the KILL command   KILL   lt transaction uow gt     References   https   docs microsoft com en-us sql t-sql language-elements kill-transact-sql view sql-server-2017 arguments  https   www mssqltips com sqlservertip 4142 how-to-kill-a-blocking-negative-spid-in-sql-server

[sql] How to find out what is locking my tables?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008-r2

Examples related to locking

Examples related to query-optimization