Thursday 8 June 2017

Finding locks on the table and all locking queries:

        sys.dm_tran_locks Metadata table contains the sessions which are currently locking a resource or waiting for a lock.

Finding if a table is locked :

you can filter on resource_associated_entity_id  with object id. 

SELECT 
from 
sys.dm_tran_locks 
where 
DB_NAME(resource_database_id) = 'dbname' 
and resource_associated_entity_id = object_id('schemaname.tablename')

Finding all locking Queries


sys.dm_exec_requests contains exec requests on the server. It contains the "sql_handle" you can pass it to "sys.dm_exec_sql_text" table values function to get the text corresponding to the handle.


SELECT 
         (select text from sys.dm_exec_sql_text(exrequests.sql_handle))
from
sys.dm_tran_locks dbl
INNER JOIN  sys.dm_exec_requests exrequests on dbl.request_session_id = exrequests.session_i
where
DB_NAME(dbl.resource_database_id) = 'dbname'

Finding the login name of the user:


sys.dm_exec_sessions contains login information of the session.

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'


Thanks for reading. Please let me know if there is any more info needed.

1 comment:

  1. I read that Post and got it fine and informative. Please share more like that... www.hashtagme.in

    ReplyDelete