How to exclude some queries in Long running query alerts

Hi,

Currently I'm using SQLsever 2012 enterprise edition. I have configured a long running query alert. My SQL server is in Always on availability group.
My Problem is this alert sent sp_server_diagnostics  - is long running. I know this is an Always on <g class="gr_ gr_22 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="22" id="22">related</g> query. So I don't need this alert. My question is how to exclude this query or this alert from my alert mail


.

Here is the query 

DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
-- specify long running query duration threshold
DECLARE @longrunningthreshold INT

SET @longrunningthreshold = 61
-- step 1: collect long running query details.
;

WITH <g class="gr_ gr_46 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="46" id="46">cte</g>
AS (
SELECT [Session_id] = spid
,[Sessioin_start_time] = (
SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id 
)
,[Session_status] = Ltrim(Rtrim([status]))
,[Session_Duration] = Datediff(s, (
SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id
), Getdate())
,[Session_query] = Substring(st.TEXT, (qs.stmt_start / 2) + 1, (
(
CASE qs.stmt_end
WHEN - 1
THEN Datalength(st.TEXT)
ELSE qs.stmt_end
END - qs.stmt_start
) / 2
) + 1)
,[Complete_Query]= (st.TEXT)
,[program_name]= qs.program_name
,[hostname] = qs.hostname
,[isblocked] = qs.blocked
FROM sys.sysprocesses qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE qs.lastwaittype<>'BROKER_RECEIVE_WAITFOR'
AND qs.cmd NOT LIKE '%BACKUP%'
AND qs.cmd NOT LIKE '%INDEX%'
AND qs.cmd NOT LIKE '%RESTORE%'
AND qs.cmd NOT LIKE '%DBCC%'
AND qs.program_name NOT LIKE '%SQLAgent - TSQL JobStep%' 
AND qs.hostname NOT LIKE '%RPT01%'                                                                                                 
 
)
-- step 2: generate html table 
SELECT @xml = Cast((
SELECT session_id AS 'td'
,''
,session_duration AS 'td'
,''
,session_status AS 'td'
,''
,[session_query] AS 'td'
,''
,[Complete_Query] AS 'td'
,''
,[program_name] AS 'td'
,''
,[hostname] AS 'td'
,''
,[isblocked] AS 'td'
FROM cte
WHERE session_duration >= @longrunningthreshold
FOR XML path('tr')
,elements
) AS NVARCHAR(max))

-- step 3: do rest of html formatting
SET @body = '
<html>
<body><bold>Long Running Queries (longer than 60 sec)</bold> 
<table border = 1 BORDERCOLOR="Black"> 
<tr>
<th align="centre"> Session_id </th> 
<th> Duration(sec) </th> 
<th> Status </th> 
<th> CurrentQuery </th> 
<th> CompleteQuery </th> 
<th> ProgramName </th> 
<th> Hostname </th>
<th> isblocked </th>
</tr>'
SET @body = @body + @xml + '</table></body></html>'

-- step 4: send <g class="gr_ gr_50 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="50" id="50">email</g> if a long running query is found.
IF (@xml IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = '*********'
,@body = @body
,@body_format = 'html'
,@recipients = '**********@*******'
,@subject = 'ALERT: Long Running Queries on DBServer';
END







August 27th, 2015 2:04am

Hello Bhuvnesh ,

Have you configured database email in your server ?

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 2:34am

If not please see here .. how to do 

Link

And put your above code in store procedure after it is able to run 

then add an job in sql agent to run this sp on your required schedule ..


August 27th, 2015 2:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics