Description

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.

SQL Statement

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"
 FROM sys.dm_exec_requests 
 WHERE blocking_session_id<>0
 order by blocking_session_id

Ignite Alert Definition