Locks details
Get current locks details:
SELECT EC2.session_id AS 'Blocker Process Id',
ES2.login_name AS 'Blocker Login Name',
ES2.program_name AS 'Blocker Program',
DB_Name(ER2.database_id) AS 'Blocked DB Name',
ES2.host_name AS 'Blocker Computer',
CA2.[text] AS 'Blocker Command',
EC1.session_id AS 'Blocked Process Id',
ES1.login_name AS 'Blocked Login Name',
ES1.program_name AS 'Blocked Program',
DB_Name(ER1.database_id) AS 'Blocker DB Name',
ES1.host_name AS 'Blocked Computer',
CA1.[text] AS 'Blocked Command',
Cast(Cast((WT1.wait_duration_ms/60000.0) As Decimal(15,2)) As VarChar) + ' min.'
AS 'Blocked Time',
(Case When WT2.session_id Is Null Then 'Top level blocker' Else 'Secondary blocker' End)
AS 'Block Level'
FROM sys.dm_exec_connections EC1
INNER JOIN sys.dm_exec_sessions ES1 ON EC1.session_id=ES1.session_id
INNER JOIN sys.dm_exec_requests ER1 ON EC1.session_id=ER1.session_id
CROSS APPLY sys.dm_exec_sql_text(EC1.most_recent_sql_handle) CA1
INNER JOIN sys.dm_os_waiting_tasks WT1 ON EC1.session_id=WT1.session_id
LEFT JOIN sys.dm_os_waiting_tasks WT2 ON WT1.blocking_session_id=WT2.session_id
INNER JOIN sys.dm_exec_connections EC2 ON WT1.blocking_session_id=EC2.session_id
INNER JOIN sys.dm_exec_sessions ES2 ON EC2.session_id=ES2.session_id
INNER JOIN sys.dm_exec_requests ER2 ON EC2.session_id=ER2.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(EC2.most_recent_sql_handle) CA2
ORDER BY 'Blocked Time' Desc
Subscribe to:
Posts (Atom)