question

Victor-1779 avatar image
0 Votes"
Victor-1779 asked BertZhoumsft-7490 commented

Sending Email Based on a Condition

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@Victor-1779

Have you tested the script below and please endorse it if it helped you, it will help someone who has a similar problem as you.

Bert Zhou

0 Votes 0 ·

1 Answer

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.