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
| WITH
T_SC AS
(
SELECT s.session_id, s.database_id, db.name AS DATABASE_NAME,
s.host_name, s.program_name, s.login_name, s.nt_domain, s.nt_user_name,
c.client_net_address, c.client_tcp_port, q.text AS QUERY_TEXT
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS q
INNER JOIN sys.databases AS db
ON s.database_id = db.database_id
),
T_BK AS
(
SELECT DATEDIFF(second, start_time, GETDATE()) AS SECOND_DURATION_REQUEST,
DATEDIFF(second, transaction_begin_time, GETDATE()) AS SECOND_DURATION_TRANSACTION,
r.session_id, r.blocking_session_id,
r.start_time, r.status, r.command, r.open_transaction_count
FROM sys.dm_exec_requests AS r
LEFT OUTER JOIN sys.dm_tran_session_transactions AS st
ON r.session_id = st.session_id
LEFT OUTER JOIN sys.dm_tran_active_transactions AS at
ON st.transaction_id = at.transaction_id
WHERE r.blocking_session_id > 0
AND DATEDIFF(second, transaction_begin_time, GETDATE()) > 3
)
SELECT 'BLOQUÉ' AS STATUT, SECOND_DURATION_REQUEST, SECOND_DURATION_TRANSACTION,
blocking_session_id, start_time, status, command, open_transaction_count,
T_SC.*, '' AS KILL_COMMAND
FROM T_BK JOIN T_SC ON T_BK.session_id = T_SC.session_id
UNION ALL
SELECT 'BLOQUANT' AS STATUT, NULL, NULL, NULL, NULL, NULL, NULL, NULL, T_SC.*,
'KILL ' + CAST(T_SC.session_id AS VARCHAR(16)) + ';'
FROM T_BK JOIN T_SC ON T_BK.blocking_session_id = T_SC.session_id
ORDER BY SECOND_DURATION_TRANSACTION DESC; |
