I want to receive a mail when a query is running for more than x seconds. I know I can set up a job running on the database but, is there any way to do the same using SQL Alerts? What kind of alert should I use? Thanks.
1 Answer
If you have sp_WhoIsActive (great tool, can be downloaded here: http://whoisactive.com/) you can log the results to a table and then email out if the threshold you set for the duration is tripped.
example:
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Load WhoIsActive To A Temp Table
IF OBJECT_ID('tempdb.dbo.#wia') IS NOT NULL DROP TABLE #wia
CREATE TABLE #wia ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)
EXEC sp_WhoIsActive
@format_output = 1,
@destination_table = '#wia'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---- Find Long Running Queries
IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #Temp
SELECT
[dd hh:mm:ss.mss],
session_id,
CAST(sql_text AS VARCHAR(MAX)) AS sql_text,
login_name,
wait_info,
host_name,
database_name,
program_name,
start_time
INTO #Temp
FROM
#wia
WHERE
start_time < DATEADD(HOUR,-8,GETDATE() ) -- CHANGE THIS TO WHATEVER YOU WANT
DECLARE @CNT INT = (SELECT COUNT(*) FROM #Temp )
IF @CNT > 0
BEGIN
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [dd hh:mm:ss.mss] AS 'td','',session_id AS 'td','', [sql_text] AS 'td','',
[login_name] AS 'td','',[wait_info] AS 'td','','',[host_name] AS 'td','',
[database_name] AS 'td','', [program_name] AS 'td','',start_time AS 'td'
FROM #Temp
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Long Running Queries</H3>
<table border = 1>
<tr>
<th> dd hh:mm:ss.mss </th> <th> session_id </th> <th> sql_text </th> <th> login_name </th>
<th> wait_info </th> <th> host_name </th> <th> database_name </th>
<th> program_name </th> <th> start_time </th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXX', -- replace with your profile
@body = @body,
@importance = 'HIGH',
@body_format ='HTML',
@recipients = 'XXXXX', -- replace with your email address
@subject = 'Longing Running Queries On <SERVER_NAME>' ;
DROP TABLE #Temp
END
