Get a mail whenever a process is taking a long time to finish

Simple monitoring of long running processes, it will mail you data about the currently long running processes if it finds any. Prereqs are that you define a database mail profile.

(See further below how to setup a database mail profile.)

This script will mail whenever it finds anything that’s been running more than 10 seconds, change the line

AND DATEDIFF(ss, r.start_time, GETDATE()) > 10

to whatever you want, but lower than 10 seconds wont help much as this script should be run in SQL Agent and it has a lower limit of 10 seconds as wait between runs.  Run it as a simple TSQL script every 10 seconds or what is good for you.

IF EXISTS ( 
	  SELECT CAST(r.session_id AS NVARCHAR(10)), db_name(r.database_id)
	FROM sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
	WHERE r.session_id > 50 
	and r.session_id=s.session_id
	and s.program_name not like '%DatabaseMail%'
	and r.session_id <> @@SPID 
	AND DATEDIFF(ss, r.start_time, GETDATE()) > 10	  
	  ) 
BEGIN	
	EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = 'DatabaseMail',
    	@recipients = 'receiver@yourdomain.com',
    	@query = 'SELECT db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
		s.login_name, 	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
		r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
		r.total_elapsed_time,r.reads,r.writes, r.logical_reads
      		FROM sys.dm_exec_requests r
      		CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt, 
			sys.dm_exec_sessions s
      		WHERE r.session_id > 50  --ensure that we omit SQL server processes
	 	AND r.session_id=s.session_id
      		AND DATEDIFF(ss, r.start_time, GETDATE()) > 10',
	@subject = 'Long running processes',
    	@attach_query_result_as_file = 1
END

 

Mail profile setup via script, change the mailhost and some other stuff to what you like before trying to send database mail.

--Enable Database Mail features on server

sp_configure 'show advanced',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

-- Create a Database Mail Account and Profile
-- !!! Remember to change display_name to your server\instance!!!

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailAccount',
@description = '',
@email_address = 'noreply@domain.com',
@display_name = 'SERVERNAME\INSTANCENAME',
@mailserver_name = 'mailhost.domain.com' ;


-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DatabaseMail',
@description = '' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DatabaseMail',
@account_name = 'MailAccount',
@sequence_number =1 ;

-- Grant access to the profile to public

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DatabaseMail',
@principal_name = 'public',
@is_default = 1 ;

 

 

Leave a Reply

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