2020. 4. 24. 14:29

현재, MS SQL에서 실행되고 있는 쿼리 및 Lock List 확인.

-- Active Session Query
SELECT

   sqltext.TEXT,

   req.session_id,

   req.status,

   req.command,

   req.cpu_time,

   req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 

-- Lock Lists

SELECT DISTINCT

        name AS database_name,

        session_id,

        host_name,

        login_time,

        login_name,

        reads,

        writes        

FROM    sys.dm_exec_sessions

        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id

        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id

WHERE   resource_type <> 'DATABASE'

AND request_mode LIKE '%X%'

AND name ='DATABASE NAME'

ORDER BY name 
 

-> kill [session_id] 

-- Active Session 

SELECT DISTINCT

        name AS database_name,

        session_id,

        host_name,

        login_time,

        login_name,

        reads,

        writes

FROM    sys.dm_exec_sessions

        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id

        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id

WHERE resource_type <> 'DATABASE'

AND name ='DATABASE NAME'

ORDER BY name

 

Trackback 0 Comment 0