SQL Tips #32 - Listing Recent Deadlock Victims

When deadlocks or blocking strike, this query lists the sessions currently being blocked and the statements involved.

The Query

SELECT
    session_id,
    start_time, 
    [status],
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    open_transaction_count,
    transaction_id,
    total_elapsed_time,
    Definition = CAST(text AS VARCHAR(MAX))
FROM
    SYS.DM_EXEC_REQUESTS
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
WHERE blocking_session_id != 0

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags