Description

This 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

Alert Definition