Sending Email Based on a Condition

Question

Hello,

I have the below script. I would like to generate an email and send it in a HTML format only if the last condition is met. I will include below script in a sql server agent job that will be running every 15 minutes and check to see there are any long running queries and if so, whether they are being blocked or not. If the conditions are met, then, need to generate an email send it to the concerned recipients possibly in an html format or a .csv file.

SELECT
r.[start_time], -- you can filter only queries that run more then 5 minutes
r.[session_id],
r.[blocking_session_id],
r.[wait_type],
r.[cpu_time],
r.[wait_time],
CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time'
, r.[status] -- you can filter only running queries
--, r.[sql_handle]
, T.[text] as SQLQuery -- you can send the query text or/and sql_handle info in the email
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.[sql_handle]) T
where CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) > '15:00:00'

Pseudocode here: If CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) is greater than '15:00:00', then, send an email to few recipients either in HTML format or .csv format.

Will greatly appreciate your input on this.
Thanks.
Victor

Answer 1

This is my proc. Feel free to modify it.

CREATE PROC [dbo].[usp_EmailLongQueries]
AS
SET NOCOUNT ON
-- Make sure dbmail is started - sometimes it stops unexpectedly
EXEC msdb..sysmail_start_sp

DECLARE @emaildba varchar(255), @subject varchar(256), @body varchar(max), @killcmd INT

SET @emaildba = 'emailaddress@nowhere.com'


DECLARE @servername varchar(256), @loginid varchar(256), @ntdomain varchar(100), @ntusername varchar(100),
    @hostname varchar(255), @hostproc varchar(10), @spid varchar(10), @execcont varchar(10), @waittype varchar(256),
    @waitresource varchar(255), @waittime varchar(10), @blockedby varchar(10), @starttime datetime, @runtimesecs varchar(10),
    @runtimemin varchar(10), @status varchar(20), @dbname varchar(255), @commandtype varchar(25), @sqllen int, @sqlcmd varchar(max), 
    @progname varchar(255), @blkcnt int

DECLARE longquery CURSOR FOR
    select 
    @@SERVERNAME as ServerName,
    sproc.loginame AS LoginID,
    sproc.nt_domain AS NTDomain,
    sproc.nt_username AS NTUserName,
    sproc.Hostname AS HostName,
    sproc.hostprocess,
    sproc.spid Session_ID,
    sproc.ecid Execution_Context,
    sproc.lastwaittype AS Wait_Type, 
    sproc.waitresource AS Wait_Resource,
    sproc.waittime AS Wait_Time, 
    CASE WHEN sproc.blocked = 0 THEN 0 ELSE sproc.blocked END as BlockedBy,
    sproc.last_batch Started_At,
    datediff(second,sproc.last_batch,getdate()) Elapsed_Seconds,
    datediff(mi,sproc.last_batch,getdate()) Elapsed_Mins,
    sproc.status Status, 
    DB_NAME(sproc.dbid) AS DBName,
    sproc.cmd Command,
    len(sqltext.TEXT) SQL_Length,
    ISNULL(SUBSTRING(sqltext.text, (sproc.stmt_start/2)+1, 
        ((CASE sproc.stmt_end
          WHEN -1 THEN DATALENGTH(sqltext.text)
          WHEN 0 THEN DATALENGTH(sqltext.text)
         ELSE sproc.stmt_end
         END - sproc.stmt_start)/2) + 1),'') AS Query_SQL,
     sproc.[program_name],
     blockingCnt = (SELECT COUNT(*) FROM master.sys.sysprocesses blk WHERE blk.blocked <> 0 AND blk.blocked = sproc.spid)
    --,sproc.*
    from master.sys.sysprocesses sproc
    OUTER APPLY master.sys.dm_exec_sql_text(sproc.sql_handle) AS sqltext
    where sproc.spid <> @@SPID
    AND sproc.spid > 50
    AND sproc.ecid = 0
    AND sproc.cmd <> 'AWAITING COMMAND'
    AND sproc.cmd NOT IN ('DBMIRRORING_CMD','WAITFOR','BACKUP')
    AND sproc.loginame <> 'sa'
    AND sproc.loginame NOT IN ('NT AUTHORITY\SYSTEM')
    AND DB_NAME(sproc.dbid) = 'xxx'     -- Specific database
    AND datediff(mi,sproc.last_batch,getdate()) > 2  -- Running longer than x mins
    ORDER BY sproc.spid, sproc.ecid


OPEN longquery
FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
        @hostname, @hostproc, @spid, @execcont, @waittype,
        @waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
        @runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @killcmd = 0

/*
    -- Kill specific select statement running > X mins
    IF @dbname='abc' 
        AND (@LoginID = 'specificlogin' OR @LoginID = 'specificlogin' OR @LoginID = 'specificlogin') 
        AND @commandtype = 'SELECT' AND @runtimemin > 10
    BEGIN
        SET @killcmd = 1
    END
*/

    IF @killcmd = 1
    BEGIN
        SET @subject = 'KILLED: ' + @servername + ' SPID: ' + @spid + ' process running for ' + @runtimemin + ' Mins'
        DECLARE @sql varchar(1000)
        SET @sql = 'KILL ' + @spid + ';'
        EXEC (@sql)
    END
    ELSE
    BEGIN
        SET @subject = CASE WHEN @blkcnt > 0 THEN 'Blocking: ' WHEN @blockedby <> 0 THEN 'Blocked: ' ELSE 'Warning: ' END + @servername + ' SPID: ' + @spid + ' Runtime: ' + @runtimemin + ' Mins'
    END

    SET @body = 'Server: ' + @servername + CHAR(10) 
    SET @body = @body + 'SPID: ' + @spid + CHAR(10) 
    SET @body = @body + 'Start Time: ' + CONVERT(varchar(25), @starttime, 100) + '  Running for: ' + @runtimemin + ' Mins' + CHAR(10) 
    SET @body = @body + 'Username: ' + @loginid + CHAR(10) 
    SET @body = @body + 'Database: ' + @dbname + CHAR(10) 
    SET @body = @body + 'Wait: ' + RTRIM(@waittype) + ' ' + @waittime + 'ms ' + CASE WHEN @blockedby <> 0 THEN 'Blocked By SPID: ' + @blockedby ELSE '' END+ CHAR(10) 
    SET @body = @body + 'From Host: ' + RTRIM(@hostname) + ' PID: ' + @hostproc + CHAR(10) 
    SET @body = @body + 'Program Name: ' + RTRIM(@progname) + CHAR(10)
    SET @body = @body + CHAR(10) + 'Query: ' + LEFT(@sqlcmd,1000) + CASE WHEN LEN(@sqlcmd)>1000 THEN ' ...more...' ELSE '' END + CHAR(10) 

    SET @body = @body + CHAR(10) + CHAR(10) + '******* THIS IS AN UNMONITORED MAILBOX.  PLEASE DO NOT REPLY TO THIS EMAIL ******* ' + CHAR(10) 

    --PRINT @subject
    --PRINT @body

    -- Send email
    EXEC msdb.dbo.sp_send_dbmail @recipients = @emaildba, @subject=@subject, @body=@body, @body_format = 'TEXT'


    FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
        @hostname, @hostproc, @spid, @execcont, @waittype,
        @waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
        @runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt
END
CLOSE longquery
DEALLOCATE longquery

SET NOCOUNT OFF

RETURN