Hi,
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 |
- Edited by BhuvaneshAadhira 1 hour 6 minutes ago