Description
This metric records the oldest active transaction time of all databases on an instance.
Adjust frequency and warnings to match expected max transaction times.
Metric Definition
To create the metric, click on Options > Custom Resource Metrics and configure similar to below.
- Database Versions: All versions
- Display Name: Oldest Active Transaction
- Description: Oldest active transaction for any database as returned by DBCC OPENTRAN
- Category: Sessions
- Units: seconds
- Metric Type: Single Value
- Frequency:180 seconds
- Timeout:30
- Warning/Critical: set per environment.
SQL Script
SET NOCOUNT ON
DECLARE @Script NVARCHAR(250)
DECLARE @Name SYSNAME
CREATE TABLE #OpenTranStatus (
ActiveTransaction VARCHAR(25)
, Details SQL_VARIANT
)
SELECT @Script = ''
DECLARE db_cursor CURSOR
FOR
SELECT name
FROM master..sysdatabases WITH (NOLOCK)
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @Name
@FETCH_STATUS = 0
BEGIN
SELECT @Script = 'INSERT INTO #OpenTranStatus EXEC(''DBCC OPENTRAN([' + @Name + ']) WITH TABLERESULTS,NO_INFOMSGS'');'
EXEC (@Script)
FETCH NEXT
FROM db_cursor
INTO @Name
END
CLOSE db_cursor
DEALLOCATE db_cursor
DELETE
FROM #OpenTranStatus
WHERE ActiveTransaction <> 'OLDACT_STARTTIME'
IF (
SELECT COUNT(1)
FROM #OpenTranStatus
) > 0
BEGIN
SELECT TOP 1 DATEDIFF(ss, CAST(Details AS DATETIME), GETDATE()) AS Seconds
FROM #OpenTranStatus
ORDER BY CAST(Details AS DATETIME) ASC
END
ELSE
BEGIN
SELECT 0 AS Seconds
END
DROP TABLE #OpenTranStatus
