locked
How to exclude some queries in Long running query alerts RRS feed

  • Question

  • 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







    • Edited by Aadhira Thursday, August 27, 2015 6:00 AM
    Thursday, August 27, 2015 5:56 AM

All replies

  • Hello Bhuvnesh ,

    Have you configured database email in your server ?


    Dilip Patil..

    Thursday, August 27, 2015 6:26 AM
  • 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 ..



    Dilip Patil..

    Thursday, August 27, 2015 6:32 AM
  • Hi Dilip,

    Mail Server is working properly. I need to ignore sp_server_diagnostics query from long running alert.


    • Edited by Aadhira Thursday, August 27, 2015 7:51 AM
    Thursday, August 27, 2015 7:50 AM