What could be causing tempdb version store growth on primary node in the absence of open/active transactions on readable secondary db?
0
votes
1
answer
482
views
This is a SQL server 2022 CU10 enterprise edition server, 2 node AG with readable secondary.
30 user dbs are in the AG.
Via an inhouse SQL monitoring tool I observed tempdb version store growth on **primary and secondary** nodes due to one of the AG dbs (let's call it ProdAppDB), the reason being a long running transaction (running since last 7 hours). Version store usage is now at 7GB, and slowly growing. Tempdb size is 128GB of which 120GB is free. But the tool isn't able to provide any further details as to where the transaction is running for so long (whether on primary or secondary node).
On Primary node, this db (ProdAppDB) is not using snapshot or rcsi isolation. Secondary replica has readable feature of the AG enabled.
Using dmvs (https://stackoverflow.com/a/45568219) I can confirm that:
- There are no active/open transactions on the readable secondary node for this db.
- There **are** active/open transactions on the primary node for this db (some are since about 8 hours prior from now).
But when I query sys.dm_exec_sessions/sys.dm_exec_requests then I don't see any query that is actively running.
Questions:
1. Why is primary node's tempdb version store growing? I am asking because rcsi/snapshot isolation is not enabled on this (or any) primary db. As per doc (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver16#bkmk_CapacityPlanning) version store shouldn't be maintained on primary when the isolation level is default.
2. Why are active/open tran on the primary node db causing version store growth on secondary replica's tempdb? I thought only long SELECTs on readable secondary could result in tempdb version store growth on the secondary node.
3. Is there anything for me to pin point to a particular to a particular process/tran/query so that I can kill just that one?
4. Is it correct to say that the only reasons for version store growth could be either due to a long running UPDATE/DELETE transaction (either actively running or sleeping/not yet committed) on primary, or a long running SELECT transaction on the readable secondary that's holding on to version store row. Or is there any other possibility?
**Update-**
Erik mentioned in a comment that this maybe unrelated to AG, and purely be cause by something else like triggers which also use version store.

Asked by variable
(3590 rep)
Dec 28, 2023, 04:09 PM
Last activity: Dec 28, 2023, 07:19 PM
Last activity: Dec 28, 2023, 07:19 PM