SQL Server How to find out who lock my specific table

Question

Here i got the script which showing bit similar output but i want output for specific table.
i want to know which query and from which PC lock my specific table. say my table name is tblOutput

here is the query which bit close to my requirement.

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

kill 104 -- Your SPID
above code taken from https://stackoverflow.com/questions/8749426/how-to-find-out-what-is-locking-my-tables

guide me with sample code which tell me which query lock my tblOutput table and from which PC?

Thanks

Answer 1

Hi @T.Zacks
To find both login name of the user and the query being run, you may check this:

SELECT DB_NAME(resource_database_id)  
     , s.original_login_name  
     , s.status  
     , s.program_name  
     , s.host_name  
     , (select text from sys.dm_exec_sql_text(exrequests.sql_handle))  
     , *  
FROM sys.dm_tran_locks dbl JOIN sys.dm_exec_sessions s ON dbl.request_session_id = s.session_id  
                     INNER JOIN sys.dm_exec_requests exrequests on dbl.request_session_id = exrequests.session_id  
WHERE DB_NAME(dbl.resource_database_id) = 'dbname'  

For more details , please refer to this article: Finding locks on the table and all locking queries

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

My stored procedure beta_lockinfo gives you the information you need.