DBILITY

mssql lock확인 본문

database/mssql

mssql lock확인

DBILITY 2021. 7. 5. 14:29
반응형
  • EXEC SP_LOCK
    Mode가 X(Exclusive Lock)인 경우 SPID를 확인
  • DBCC INPUTBUFFER(SPID)
    Client의 마지막 statement를 표시
  • EXEC SP_WHO SPID
    STATUS, LOINGNAME, DBNAME등 SPID정보를 출력
  • KILL SPID
    SPID에 해당하는 프로세스를 종료
#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 LIST
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;​
반응형
Comments