반응형
/*
-- Title : [SQL2017] sp_who2, sp_whoisactive가 안될 때 Lock 조회 쿼리
-- Reference : 구글링
*/
■ 조회쿼리-1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SELECT p.status ,p.program_name ,p.hostname ,p.spid ,p.blocked ,p.kpid
, p.cpu ,p.physical_io ,p.waittype ,p.waittime ,p.lastwaittype
, p.waitresource ,p.dbid ,p.uid ,p.memusage ,p.login_time ,p.last_batch
, p.ecid ,p.open_tran ,p.sid ,p.hostprocess
, p.cmd ,p.nt_domain ,p.nt_username ,p.net_address
, p.net_library ,p.loginame ,p.context_info ,p.sql_handle
, p.stmt_start ,p.stmt_end
FROM master..sysprocesses p
WHERE (
STATUS LIKE 'run%'
OR waittime > 0
OR blocked <> 0
OR open_tran <> 0
OR EXISTS (
SELECT *
FROM master..sysprocesses p1
WHERE p.spid = p1.blocked
AND p1.spid <> p1.blocked
)
)
AND spid > 50
AND spid <> @@spid
ORDER BY CASE
WHEN STATUS LIKE 'run%'
THEN 0
ELSE 1
END
, waittime DESC
, open_tran DESC
|
cs |
■ Lock 조회 및 해제
> EXEC sp_lock
> DBCC inputbuffer(@spid)
> EXEC sp_who
> EXEC sp_who @spid
> EXEC KILL @spid
■ Lock 조회 쿼리-2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
-- 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
|
cs |
반응형