Situatie
Mai jos este o procedura care se ruleaza pe un server de sql si returneaza toate sesiunile ce sunt implicate in blocaje, furnizand cateva date despre acestea.
Solutie
create procedure FindBlockingSessions
as
SELECT
client_net_address as HOSTIP,
Blocking.session_id as BlockingSessionId ,
Sess.login_name AS BlockingUser ,
BlockingSQL.text AS BlockingSQL ,
Waits.wait_type WhyBlocked ,
Blocked.session_id AS BlockedSessionId ,
USER_NAME(Blocked.user_id) AS BlockedUser ,
BlockedSQL.text AS BlockedSQL ,
DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id
JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id
RIGHT JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId
Leave A Comment?