mssql lock확인

• 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;​