Email Alert of Long Uncommitted Transactions

Long running transaction in database causes performance impact to your system. Process blocking and deadlock will be arisen by lock waits on the resources being acquired by the long running transaction. Transaction log space cannot be reclaimed as there is an active transaction, the transaction log eventually out of space and the database becomes inaccessible (read-only). Also, if you have turned on READ_COMMITTED_SNAPSHOT for a database, SQL Server saves the before image of the data before starting a transaction in tempdb. This is called version store. If a transaction is running for a long time, the version store does not get cleared and it continues to grow filling up tempdb data file.
In order to monitor any long running (uncommitted) transactions in your database, you can create an email alert to send notification to your DBA team using Database Mail. Thanks for the script provided by Paul, I wrote the below script to send such email alert. You can create a SQL Agent Job, paste this script as the job step, and schedule the job to run periodically. (This script uses another stored procedure ConvertTableToHtml to render the result as HTML table, you should create this stored procedure before creating your job).

---

SET NOCOUNT ON;

-- **** Threshold in seconds ****
DECLARE @timeoutSec int = 15

DECLARE @recipients varchar(max), @subject nvarchar(255), @body nvarchar(max)

-- **** EMAIL RECIPIENTS, semicolon-delimited ; ****
SET @recipients = 'peter.lee@guosen.com.hk'

SET @subject = 'Long Uncommitted Transaction'
SET @body = 'Uncomitted transactions running longer than specified threshold ' + CAST(@timeoutSec AS varchar(10)) + ' seconds.'

DECLARE @htmlTable varchar(max)
CREATE TABLE #t (
[session_id] int,
[Login Name] nvarchar(128),
[Database] nvarchar(128),
[Begin Time] datetime,
[Duration (min.)] varchar(50),
[Last T-SQL Text] nvarchar(max)
)

INSERT #t SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
CAST(CAST(DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) / 60.0 AS decimal(9, 1)) AS varchar(50)) AS [Duration (min.)],
[s_est].text AS [Last T-SQL Text]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
WHERE DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) > @timeoutSec
AND s_tdt.database_id <> DB_ID('msdb') -- EXCLUDE msdb system database
ORDER BY
[Begin Time] ASC;

DECLARE @rowcount bigint
SELECT @rowcount = COUNT(*) FROM #t

IF @rowcount > 0
BEGIN
--Sending Mail
EXEC master.dbo.ConvertTableToHtml 'SELECT * FROM #t', @htmlTable OUTPUT
SET @body = @body + '<br/>' + @htmlTable
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @body,
@body_format = 'HTML',
@subject = @subject,
@importance = 'High';
END

DROP TABLE #t

---

Sample result:

Be aware that this script excluded the msdb database, as SQL Server uses this system database to run some background tasks.