query – SQL alert for long-term queries

If you have sp_WhoIsActive (great tool, you can download it here: http://whoisactive.com/), you can connect the results in a table, then send an email if the threshold you set for the duration is triggered .

Example:

-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------------------
---- Load WhoIsActive in a temporary 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] NULL date-time,[request_id] int NULL,[collection_time] datetime NOT NULL)

EXEC sp_WhoIsActive
@format_output = 1,
@destination_table = # wia & # 39;

-------------------------------------------------- ------------------------------
-------------------------------------------------- ------------------------------
---- Find long-term queries


IF OBJECT_ID (# Tempdb.dbo # Temp) is NOT NULL DROP TABLE #Temp

TO SELECT 
[dd hh:mm:ss.mss],
Session ID,
CAST (sql_text as VARCHAR (MAX)) AS sql_text,
username,
wait_info,
hostname,
Name of the data base,
name of the program,
Start time
INTO #Temp
OF
#wia

OR
Start time < DATEADD(HOUR,-8,GETDATE() ) -- CHANGE THIS TO WHATEVER YOU WANT

DECLARE @CNT INT = (SELECT COUNT(*) FROM #Temp )

IF @CNT > 0
TO START
-------------------------------------------------- -------------------------------------------------- ------------------
-------------------------------------------------- -------------------------------------------------- ------------------



DECLARE @ xml NVARCHAR (MAX)
DECLARE @body NVARCHAR (MAX)


SET @xml = CAST ((SELECT [dd hh:mm:ss.mss] AS 'td', 'session_id AS', 't' ',' ' [sql_text] AS '#' s, "no," 
[login_name] AS '#' s, "no,"[wait_info] AS'd, "no,"[host_name] AS '#' s, "no,"
[database_name] AS '#' s, "no," [program_name] AS 'td', 'start_time AS'; td '
Of time
FOR XML PATH (& # 39;), ELEMENTS) UNDER NVARCHAR (MAX))



SET @body = & # 39;

Long queries

& # 39; SET @body = @body + @xml + & # 39;
dd hh: mm: ss.mss Session ID sql_text login wait_info hostname Name of the data base name of the program Start time
& # 39; EXEC msdb.dbo.sp_send_dbmail @profile_name = & # 39; XXXXX & # 39 ;, - replace with your profile @body = @body, @importance = & # 39; HIGH & # 39; @body_format = & # 39; HTML & # 39; @recipients = "XXXXX" - replace with your email address @subject = Longing Running Queries On & # 39 ;; DROP TABLE #Temp END