DBA_Blocking_Alerts script

 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

No comments:

Post a Comment