SQL Server / Long Running Queries / Email Alert

Posted by in SQL Server, SQL Tips and Tricks on May 4th, 2018 | 1 comment

This is example of long running queries of database within email alert, we already had example how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily, and how to send missing indexes report via email. This is just another use case of sp_send_dbmail stored procedure.

I used following arguments to achieve this

@profile_name (name of an existing Database Mail profile, type sysname, default NULL),

If you are not sure about your profile account use the following script, it shows all the accounts for all profiles in the instance.

 

@recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)),

@body (Is the body of the e-mail message, nvarchar(max)),

@subject (Is the subject of the e-mail message, nvarchar(255)),

@body_format (Is the format of the message body, varchar(20))

More information about this system stored procedure you can find here sp_send_dbmail (Transact-SQL)

 

 

Your email result should be similar to

 

After you have sql handle of your expensive query you can then get XML query plans or any other query stats information

 

 

Similar Posts:

One Response to “ “SQL Server / Long Running Queries / Email Alert”

  1. ram says:
    April 19, 2022 at 2:52 pm

    Hi Sir,

    could you please help me, how to get below query report like your report…
    _________________________________________

    DECLARE @xml NVARCHAR(max)
    DECLARE @body NVARCHAR(max)
    — specify long running query duration threshold
    DECLARE @longrunningthreshold int
    SET @longrunningthreshold=0
    –step 1: collect long running query details.
    ;WITH cte
    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(mi, (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)
    FROM sys.sysprocesses qs
    CROSS apply sys.Dm_exec_sql_text(sql_handle) st)

    –step 2: generate html table
    SELECT @xml = Cast((SELECT d.session_id AS ‘td’,
    ”,
    d.session_duration AS ‘td’,
    ”,
    d.session_status AS ‘td’,
    ”,
    d.session_query AS ‘td’
    FROM cte d
    WHERE session_duration >= @longrunningthreshold
    FOR xml RAW(‘tr’), elements) AS NVARCHAR(max))

    –step 3: do rest of html formatting
    SET @body =
    ‘Long Running Queries ( Limit > 1 Minute ) Session_id Session_Duration(Minute) Session_status Session_query’
    SET @body = @body + @xml + ”

    –step 4: send email if a long running query is found.
    IF( @xml IS NOT NULL )
    BEGIN
    EXEC msdb.dbo.Sp_send_dbmail
    @profile_name = ‘SQLAlert’,
    @body = @body,
    @body_format =’html’,
    @recipients = ”,
    @subject = ‘ALERT 1234 Long Running Queries’;
    END

    Result:
    Long Running Queries ( Limit > 1 Minute ) Session_id Session_Duration(Minute) Session_status Session_query188399suspendedsp_server_diagnostics2420runnableWITH cte 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(mi, (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) FROM sys.sysprocesses qs CROSS apply sys.Dm_exec_sql_text(sql_handle) st) –step 2: generate html table SELECT @xml = Cast((SELECT d.session_id AS ‘td’, ”, d.session_duration AS ‘td’, ”, d.session_status AS ‘td’, ”, d.session_query AS ‘td’ FROM cte d WHERE session_duration >= @longrunningthreshold FOR xml RAW(‘tr’), elements) AS NVARCHAR(max))2441suspendedWAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout4260suspendedINSERT INTO “”..”NCM_Cisco_FlashFiles” (“NodeID”,”LastDiscovery”,”FirstDiscovery”,”Missing”,”FlashFileName”,”FlashFileSize”,”FlashCheckSum”,”FlashFileStatus”) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *