August 6, 2025 at 12:02 pm
I'm looking for an extended event recommendation that will send an email when a transaction runs longer than 5 minutes.
Thanks in advance
August 6, 2025 at 1:16 pm
Extended Events can't do things. They're monitoring. They observe things. You could code something that sends an email in response to an Extended Event, absolutely. However, the Extended Event is not doing the action. It's recording that something happened within SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2025 at 2:00 pm
here's an example i use pretty frequently. it's a stored procedure, that is calling Adam Machanic's sp_whoisactive.
if there are no long running transactions, It bails out gracefully, but if something hits the threshold and hits the limit and exceeds the @ExpectedThreshHoldInMinutes, then it sends an email.
after that, it's just calling this stored procedure in a job every x minutes.
note the output tends to be very wide and detailed, so after you test it, you can eliminate column data that you don't feel you need, but me being a DBA, i like too much information, so i don't have to look deeper.
--#################################################################################################
-- Standard Procedure to do the work: drop the Procedure if it exists, replacing with this current code.
--#################################################################################################
IF OBJECT_ID('[dbo].[usp_DBALongTransactionMonitor]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_DBALongTransactionMonitor]
GO
CREATE PROCEDURE [dbo].[usp_DBALongTransactionMonitor] @ExpectedThreshHoldInMinutes int = 30
AS
BEGIN
--#################################################################################################
-- Code to capture event and email long running transactions(transaction_start_time not start_time)
-- that are in an open uncommitted transaction
--#################################################################################################
DECLARE @DebugMode INT = 0,
@DebugEmail VARCHAR(128) = 'admin@yourdomain.com'
--DECLARE @ExpectedThreshHoldInMinutes int = 30 --50 minutes, less than one hour
--desc: if an extended property for the cname was created, list it here.
--verify the cname
DECLARE @ServerName VARCHAR(128)
DECLARE @AppEmailGroup VARCHAR(128)
SELECT
@AppEmailGroup = CONVERT(VARCHAR(4000),prpz.value) --AS CName
FROM [master].sys.[extended_properties] prpz
WHERE prpz.Class=0
AND prpz.name = 'AppEmailGroup'
SET @AppEmailGroup = ISNULL(@AppEmailGroup,'')
SELECT
@ServerName = CONVERT(VARCHAR(128),prpz.value) --AS CName
FROM [master].sys.[extended_properties] prpz
WHERE prpz.Class=0
AND prpz.name = 'Cname'
SELECT @ServerName = ISNULL(@ServerName,CONVERT(VARCHAR(128),@@SERVERNAME))
--desc: sp_whoisactive with transactions and more
IF OBJECT_ID('tempdb.[dbo].[#whoisactiveCapture]') IS NOT NULL
DROP TABLE [dbo].[#whoisactiveCapture]
CREATE TABLE [#whoisactiveCapture]
(
[dd hh:mm:ss.mss] VARCHAR (8000) NULL,
[session_id] SMALLINT NOT NULL,
[sql_text] XML NULL,
[sql_command] 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,
[tran_start_time] DATETIME 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
);
EXECUTE [dbo].[sp_WhoIsActive]
@get_outer_command=1,
@output_column_list = '[dd hh:mm:ss.mss],[session_id],[sql_text],[sql_command],[login_name],[wait_info],[CPU],[tempdb_allocations],[tempdb_current],[blocking_session_id],[reads],[writes],[physical_reads],[used_memory],[status],[tran_start_time],[open_tran_count],[percent_complete],[host_name],[database_name],[program_name],[start_time],[login_time],[request_id],[collection_time]',
@get_full_inner_text = 1,
@show_sleeping_spids = 2,
@get_transaction_info = 1,
@show_system_spids = 0,
@destination_table = '[#whoisactiveCapture]';
IF EXISTS(SELECT DATEDIFF(SECOND,[tran_start_time],GETDATE()) AS TransactionOpenInSeconds,*
FROM #whoisactiveCapture
WHERE session_id > 50
AND tran_start_time IS NOT NULL
AND DATEDIFF(SECOND,[tran_start_time],GETDATE()) > (@ExpectedThreshHoldInMinutes * 60) --minutes to seconds
)
BEGIN
PRINT 'Long Running Transactions Found'
--#################################################################################################
-- Taking too long, raise the alert.
--#################################################################################################
PRINT 'Raising the alert for Long Transactions'
--#################################################################################################
-- Taking too long, raise the alert.
--#################################################################################################
DECLARE @HTMLBody VARCHAR(MAX),
@HTMLSubject VARCHAR(MAX),
@TableHead VARCHAR(MAX),
@TableTail VARCHAR(MAX);
SET @TableTail = '</table></body></html>';
--[Cname],[ServerName],[Issue],[ElapsedTime dd:hh:mm:ss],[ExpectedThreshHoldInHours],[ObjectName],[FolderName],[ProjectName],[PackageName],[caller_name],[server_name]
SET @TableHead = '<html><head>'
+ '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
+ '</style>'
+ '</head>'
+ '<body>This Report is identifying Long Running Transactions taking longer than ExpectedThreshHoldInMinutes of ' + CONVERT(VARCHAR(30),@ExpectedThreshHoldInMinutes)
'
+ ' on the server ' + @ServerName + '(' + CONVERT(VARCHAR(128),@@SERVERNAME) + ').<br />
It is <b>probable</b> that some process is slowing down or blocking the transaction, and requires immediate attention.<br />
DBA Team must check for any locking, blocking, long running queries, index or statistics operations that might contribute to the slowness before alerting additional teams.<br />
<b> This issue is a top priority.</b>
+ '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#FFEFD8>'
+ '<th align=center><b>[CName]</b></th>'
+ '<th align=center><b>[ServerName]</b></th>'
+ '<th align=center><b>[TransactionElapsedTime dd hh:mm:ss.mss]</b></th>'
+ '<th align=center><b>[TransactionTimeInSeconds]</b></th>'
+ '<th align=center><b>[wait_info]</b></th>'
+ '<th align=center><b>[login_name]</b></th>'
+ '<th align=center><b>[host_name]</b></th>'
+ '<th align=center><b>[database_name]</b></th>'
+ '<th align=center><b>[program_name]</b></th>'
+ '</tr>';
SET @HTMLSubject = 'Long Running Transactions on ' + @ServerName + ' exceed the current max threshold of ' + CONVERT(VARCHAR,@ExpectedThreshHoldInMinutes) + ' Minutes (' + CONVERT(VARCHAR,CONVERT(MONEY,(@ExpectedThreshHoldInMinutes / 60.0))) + ') minutes.'
SELECT @HTMLBody =
(SELECT
ROW_NUMBER() OVER(ORDER BY [whoz].[tran_start_time]) % 2 AS [TRRow],
@ServerName AS ,
CONVERT(VARCHAR(128),@@servername) AS ,
ISNULL(([dt].[Days] + ':' + [dt].[Hours] + ':' + [dt].[Minutes] + ':' + [dt].[Seconds]),'') AS ,
ISNULL(DATEDIFF(SECOND,[whoz].[tran_start_time],GETDATE()),'') AS ,
ISNULL([whoz].[wait_info],'') AS ,
ISNULL([whoz].[login_name],'') AS ,
ISNULL([whoz].[host_name],'') AS ,
ISNULL([whoz].[database_name],'') AS ,
ISNULL([whoz].[program_name],'') AS ,
ISNULL(LEFT(CONVERT(VARCHAR(MAX),ISNULL([whoz].[sql_text],'')),600),'') + '...' AS [trtdcs6], --TR>TD Colspan=6
ISNULL(LEFT(CONVERT(VARCHAR(MAX),ISNULL([whoz].[sql_command],'')),600),'') + '...' AS [trtdcs5] --TR>TD Colspan=5
FROM [#whoisactiveCapture] [whoz]
CROSS APPLY(SELECT
[Days] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(DAY,GETDATE() - [tran_start_time])-1),2),
[Hours] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(HOUR,GETDATE() - [tran_start_time])),2),
[Minutes] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(MINUTE,GETDATE() - [tran_start_time])),2),
[Seconds] = RIGHT('000' +CONVERT(VARCHAR(128),DATEPART(SECOND,GETDATE() - [tran_start_time])),2)
) [dt]
WHERE [whoz].[session_id] > 50
AND [whoz].[open_tran_count] IS NOT NULL
AND DATEDIFF(SECOND,[whoz].[tran_start_time],GETDATE()) > @ExpectedThreshHoldInMinutes
ORDER BY [whoz].[tran_start_time]
FOR XML RAW('tr'), ELEMENTS);
-- Replace the entity codes and row numbers
SET @HTMLBody = REPLACE(@HTMLBody, '_x0020_', SPACE(1));
SET @HTMLBody = REPLACE(@HTMLBody, '_x003D_', '=');
SET @HTMLBody = REPLACE(@HTMLBody, ' ', CHAR(13) + CHAR(10));
SET @HTMLBody = REPLACE(@HTMLBody, '<trtdcs6>', '</tr><tr><td colspan="6">');
SET @HTMLBody = REPLACE(@HTMLBody, '</trtdcs6>','</td>');
SET @HTMLBody = REPLACE(@HTMLBody, '<trtdcs5>', '<td colspan="5">');
SET @HTMLBody = REPLACE(@HTMLBody, '</trtdcs5>', '</td></tr>');
SET @HTMLBody = REPLACE(@HTMLBody, CHAR(10), CHAR(10) + '<br />');
SET @HTMLBody = REPLACE(@HTMLBody, '<br /><br />', '<br />');
SET @HTMLBody = REPLACE(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>');
SET @HTMLBody = REPLACE(@HTMLBody, '<TRRow>0</TRRow>', '');
SELECT @HTMLBody = @TableHead + @HTMLBody + @TableTail;
--SELECT @HTMLSubject = 'Critical: Long Running Transactions On ' + @ServerName + ' exceeding @ExpectedThreshHoldInMinutes of ' + CONVERT(VARCHAR,@ExpectedThreshHoldInMinutes) + ' Minutes (' + + CONVERT(VARCHAR(30),@ExpectedThreshHoldInMinutes / 60.0) + ' Hours)'
--SELECT @HTMLBody
IF @AppEmailGroup > ''
SET @AppEmailGroup = @AppEmailGroup + ';admin@yourdomain.com'
ELSE
SET @AppEmailGroup = 'admin@yourdomain.com'
IF @DebugMode = 1 SET @AppEmailGroup = @DebugEmail
--SELECT @HTMLBody
EXECUTE [msdb].[dbo].[sp_send_dbmail]
@profile_name = NULL, -- sysname
@recipients = @AppEmailGroup,
@subject = @HTMLSubject,
@body = @HTMLBody,
@body_format = 'HTML';
END
ELSE
BEGIN
PRINT 'No long Running Transactions'
END
END
GO
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply