We have created an SQL alert in Sql server 2016 for the counter as below to monitor the Long running transactions.
SQL Server Performance Condition Alert InstanceName-Transactions Transactions rises above 40 Sec
In response to this alert, a job logs results from below DMs into a table.
FROM sys.sysprocesses sp1
INNER JOIN sys.dm_exec_sessions ES ON ES.session_id = sp1.spid
INNER JOIN sys.dm_db_session_space_usage SU ON SU.session_id = sp1.spid
LEFT JOIN sys.dm_exec_query_memory_grants MG ON MG.session_id = sp1.spid
LEFT JOIN sys.dm_exec_requests R on r.session_id = sp1.spid
LEFT JOIN sys.dm_os_waiting_tasks OSW ON OSW.session_id = sp1.spid
OUTER APPLY sys.dm_exec_sql_text(sp1.sql_handle) ss
left JOIN sys.databases AS d on d.database_id = ES.database_id
where sp1.spid > 50
Now I have two concerns.
-> Longest Transaction Running Time cntr logs values in "READ COMMITTED SNAPSHOT" isolation level only. On server at no DB level this is enabled (is_read_committed_snapshot_on is 0).
So how is this alert getting triggered at all?
-> When checking in the logging tables, I do not find any running session for which DATEDIFF(SECOND,r.start_time,getdate()) is > 40.
Any guesses why this is not logging any such session. If this alert is triggered then there should be at least one session that should be running for >40 seconds.