Below mentioned script will send the blocking queries which are blocked for more than 5 minutes and we need to create job for below script to run every 10 minutes
SET
NOCOUNT ON
DECLARE
@xml nvarchar(max)
SELECT
@xml = Cast((SELECT @@SERVICENAME 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
= 'sqlmail',
@body = @body,
@body_format
='html',
@recipients
= 'bajeyudu@sqldbanow.com',
@copy_recipients
='ajay@sqldbanow.com', -- list of Email
recipients
@subject = 'Blocking queries
Alert-SQLDBANOWDB01';
END
SET
NOCOUNT OFF
No comments:
Post a Comment