Recently I needed to find a stored procedure that was creating a lock on a table and updating the table regularly. So my starting point was the table name, but I needed to find where the commad was coming from that was locking the table, and what stored procedure was doing the update, if any. As I could not be sure that it was a sproc, I wanted to make sure that I also got the program name.

A simple version of the query would look like this:

 --simple

SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = OBJECT_ID(N'CciDemo')

But this does not get full info. But by joining on the dmv exec_sessions and exec_requests on the corresponding session id columns, and by cross applying on the exec_sql_text on the exec_request sql_handle column I can get the full text of the command. I can also narrow down the lock requests types to only the update lock types:

--verbose

SELECT sesh.program_name, sesh.host_name, st.text, sesh.login_name, lock.request_mode, lock.request_session_id
FROM sys.dm_tran_locks lock
INNER JOIN sys.dm_exec_sessions sesh ON sesh.session_id = lock.request_session_id
INNER JOIN sys.dm_exec_requests req ON req.session_id = lock.request_session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
WHERE lock.resource_database_id = DB_ID()
AND lock.resource_type = 'OBJECT'
AND lock.resource_associated_entity_id = OBJECT_ID(N'CciDemo')
AND lock.request_mode IN ('X', 'U', 'IX')

The results of the queries whilst I was running a long running update on a table is below.