question

UdhamSingh-6769 avatar image
0 Votes"
UdhamSingh-6769 asked UdhamSingh-6769 commented

Longest Transaction Running Time-Alert getting triggered even when read_committed_snapshot not enabled

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered UdhamSingh-6769 commented

Hi UdamSingh-6769,
Unfortunately, for the SQL Server Transactions counter 'Longest Transaction Running Time', it only shows activity when the database is under read committed snapshot isolation level, and does not log any activity if the database is in any other isolation level. Please refer to this doc.
And please refer to this article which might be helpful.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just to add, this counter also log value when you have enabled the SNAPSHOT isolation level.

0 Votes 0 ·