TEMPDB Version Store Growth when READ_COMMITTED_SNAPSHOT IS OFF
-
Why would the version store be growing for a database where READ_COMMITTED_SNAPSHOT is OFF?
SELECT U.*,D.name,is_read_committed_snapshot_on
FROM tempdb.sys.dm_tran_version_store_space_usage U
JOIN sys.databases D on U.database_id = D.database_id
WHERE reserved_space_kb > 0
order by reserved_space_kb desc
Thanks
-
Is ALLOW_SNAPSHOT_ISOLATION on?
-
snapshot_isolation_state = 0 for all the databases listed above.
-
Is this part of an availability group?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Availability groups are not enabled on the server. Thanks.
-
It turns out the virtual store is being used up by a trigger.
There's a nice explanation here: https://www.itprotoday.com/sql-server/triggers-and-version-store
My other mystery is why the virtual store space isn't necessarily freed up once the update/insert/delete has completed and a minute has passed for the system cleanup to run. It has to do with some other long-running transaction(s) but, as far as I know, they're not accessing the database with the trigger.
I've noticed this also with the virtual store and a database with READ_COMMITTED_SNAPSHOT where the space won't be freed up until some long-running transactions complete (again, I don't think they're accessing the database with the READ_COMMITTED_SNAPSHOT setting).
-
The second scenario makes sense -- read-committed snapshot is going to maintain the version store until transaction is completed regardless of the isolation used by that transaction, because reads by other transactions under read-committed snapshot must still use the old version of data until the transaction completes.
I don't know what the expected lag is for cleanup.
-
That is very interesting - learn something new every day. Thanks for the update.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
tempdb version store is always used whenever snapshot isolation is used with any query or database !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply