Good Morning All,
I am observing an intermittent problem on one of our instances that's in a Synchronous AlwaysOn Availability.
We have 2 instances, 2 AG's with about 70 DB's in each AG.
One principle group hosted on each instance.
Database sizes ranges from 1GB to 1TB
Tempdb settled at about 1.2 TB
The Version Store at times is pretty big, right now at the tail end of peak load its 600+ GB:
user_obj_MB, internal_obj_MB, version_store_MB freespace_MB, mixedextent_MB
126, 42, 616026, 699293, 510
At times (though they seem random, and not to coincide with any jobs nor user queries) we run out of tempdb space, and its t he Version store that consumes most of it.
What can cause this? We do query the read only replica - though (allegedly) not heavily but at the moment I can't quantify this - is there a way on the read only replica to monitor this (there is no read only routing configured so they will be direct connections)
What else can cause the version store to grow like this? We dont have intentional rcsi, we dont have any online index rebuilds, all the queries use the default isolation.
I am at a bit of a loss now how to track this further. As always your help is much appreciated.
Cheers
Alex