SQL HA Cluster TempDB Version Store blocking on secondary Replica due to open transaction?

Question

I am currently investigating a repeating error which occurs on the secondary Replica of our 2 node Alwasy on High Availability cluster. The Replica is set up with Read-Intent only because we use a separate Backup solution (Dell Networker).

The Tempdb keeps growing in the secondary replica because the Version Store never gets cleared. I can fix it temporarly when i failover the Availability Groups, but after a couple of hours the error appears again on the repilca node. The error seems to follow one specific Availabilty Group, every node where its currently replicating gets the error after some time. So i guess it has to be a issue caused by a transaction and not from the sytem itself.

I tried all suggestions on google to find the culprit but even if i recklessly kill all sessions with last_batch in the timeframe i get from the Perfmon "longest running transaction time" indicator (as advised here: https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server ), it won't start cleaning up the Versionstore.

The shown elapsed seconds also match the output of the Query on the Secondary node: select * from sys.dm_tran_active_snapshot_database_transactions

details are sadly not usefull:

Output

Here it shows Transaction ID 0 and Session ID 823 but the Session ID is long gone and keeps getting used by other processes alread. So i am stuck here. I tried to match the Transaction_sequence_num with anyting, but no luck so far.

On the Primary Node it show no open transactions of any kind.

Any help finding the cause of this open snapshot transaction is appreciated.

I followed this guides already to find the issue:

https://sqlundercover.com/2018/03/21/tempdb-filling-up-on-secondary-replicas/

https://sqlgeekspro.com/tempdb-growth-due-to-version-store-in-alwayson/

https://learn.microsoft.com/en-us/archive/blogs/docast/rapid-growth-of-tempdb-on-alwayson-secondary-replica-due-to-version-store

https://www.sqlshack.com/how-to-detect-and-prevent-unexpected-growth-of-the-tempdb-database/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/635a2fc6-550b-4e08-b232-0652bd6ea17d/version-store-space-not-being-released?forum=sqldatabaseengine

https://www.sqlservercentral.com/articles/tempdb-growth-due-to-version-store-on-alwayson-secondary-server

Update:

To show my claim that the session is long gone: Here the Pictures you see the output of sys.dm_tran_active_snapshot_database_transactions, sys.sysprocesses and sys.dm_exec_sessions: The first Picture shows currently 2 "open" snapshot database transactions. (normally it was always one in the past, but maybe the more the better) and it shows the now to this time running sessions on this ids.

Than i proceeded to kill session 899 and 823 and checked again: Here you can see the active_snapshot_database_transaction is still showing the 2 Session_ids and the sysprocesses and dm_exec_sessions show now the 2 IDs are in use by a different Program, User, database etc. because i killed them and the ID number immediately got reused. If i check through the day sometimes they are even not in use at all.

If i check the elapsed time and the perfmon longest running transaction i would be looking for a session with a logintime or batch at aroung 2023-02-03 00:00:56. But if i check all sleeping sessions or sessions with last batch in this range and even kill all of them ( like described in all of the links above) it still shows the "transaction" in sys.dm_tran_active_snapshot_database_transactions with ever growing numbers.

Update 2:

in the meantime we needed to resolve the issue with a failover because the tempdb ran out of space. Now the new "stuck" session id as shown in dm_tran_active_transaction has the session id 47 and is currently at around 30000sec rising. So the problem started at around 11.2.2023 00:00:20. Here is the Output of dm_tran_active_transaction:

Answer 1

There are many different assumptions going on here which need to be addressed.

First, the mechanism by which availability groups work is by log blocks. They don't send individual transactions. Log blocks are written to the log on the secondary and eventually the information inside of them is redone.

Second, the version store is only used on readable secondary replicas. Thus, looking at the primary for sessions that are using items on the secondary is not going to help. The version store can only be cleaned up by removing the oldest unused versions until it hits a version in use, it cannot skip versions, thus is version 3 is needed but 4-10 aren't, anything below 3 can be cleaned up but anything (including 3) can't.

Third, if a session is closed then any outstanding items are cleaned up. Whether that is freeing memory, killing transactions, etc. There is no evidence that the session is actually disconnected on your secondary replica that was given.

I wrote this in lieu of adding comments. If the OP decides to add in more data on the secondary they can and I'll address it. The replica can also be changed to not readable and that will solve the problem, since the issue is queries on the secondary.

Answer 2

I could find out the source Database which causes the issue. Since i was too focused on the Secondary Replica i went back and checked all Databases of the affected Availability Group one by one with DBCC Opentran. And got a match on the Starttime of one output exactly when the issue occurs.

Now i imagine the reason why i am not seeing it somewhere else is probaply: sys.dm_exec_sessions shows a always recent Last_request_start_time and last_request_end_time so something is happening on the Session. But sys.dm_tran_database_transactions shows a transaction_id on this session running for X hours (start time of the issue) with Database Transaction state 4. Also sys.dm_tran_locks shows a Schema (Sch-S) Lock caused by this transaction.

So i consider my first question as answered because the following analysis is a different question in it self.