Custom Ignite Alert: SQL Server Locking in Instance-Individual Session#LR14335
This custom Ignite alert will run against the monitored instance and retrieve the time a session has been waiting ("Seconds Blocked" column) on a blocking session. It will also give some details about the blocking session in the "Message" column and this can be customized with anything else from sys.dm_exec_requests.
SELECT 'SPID ' + convert(varchar,blocking_session_id) +
' has been blocking ' + CONVERT(varchar,session_id) +
' for ' + CONVERT(varchar,wait_time/1000) + ' seconds.' "Message", wait_time/1000 "Seconds Blocked"
order by blocking_session_id
Ignite Alert Definition