0

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.

enter image description here

  1. 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.

  2. 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?

  3. 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.

1 Answer 1

3

On Primary node, this db (ProdAppDB) is not using snapshot or rcsi isolation. Secondary replica has readable feature of the AG enabled.

Much as I answered in this question, regardless of having the database level settings enabled, readable secondary replicas will cause read committed to be silently remapped to snapshot. This uses the version store.

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.

It is enabled, you enabled it by making the secondary database readable.

Why are active/open tran on the primary node db causing version store growth on secondary replica's tempdb? I thought only active/open tran on readable secondary could result in tempdb version store growth on the secondary node.

It can and will do both.

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?

Lots of answers and question about this, I would encourage you to read them.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.