Please find the below mentioned script help you get an alert..
SET NOCOUNT ON
DECLARE @xml nvarchar(max)
SELECT @xml = Cast((SELECT HOST_NAME() AS 'td','',b.session_id AS 'td',
'',
(b.wait_duration_ms/1000)/60 AS 'td',
'',
b.wait_type AS 'td',
'',
b.blocking_session_id AS 'td',
'',
t.text AS 'td'
FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id
OUTER APPLY
sys.dm_exec_sql_text(sql_handle) t
WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>300000
FOR xml path('tr'), elements) AS NVARCHAR(max))
Declare @body nvarchar(max)
SET @body =
'<html>
<head>
<style>
table, th, td
{
border: 1px solid black;
border-collapse: collapse;
text-align: center;
}
</style>
</head>
<body>
<H2>
Blocking queries
</H2>
<table>
<tr>
<th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th>
<th> Blocking Session ID</th><th>Query waiting to execute</th>
</tr>'
SET @body = @body + @xml + '
</table>
</body>
</html>'
if(@xml is not null)
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'dbmail',
@body = @body,
@body_format ='html',
@recipients = 'ajay@sqldbanow.com',
@copy_recipients ='bandarucreations@sqldbanow.com', -- list of Email recipients
@subject = 'Blocking queries Alert-SQLDBANOW01';
END
SET NOCOUNT OFF
