Unexpected Databases showing in tempdb version store graph
SEarle86
Posts: 21 Bronze 1
I am seeing some unexpected databases showing in the tempdb version store graph on the server overview page:
We can see msdb, and some user databases I have named T1, T2, B1, B2 and B3, are all using the version store at various points in this 24 hour period
If I run the following query against the server, it returns everything I would expect to use the version store
The results are as follows:
B1, T1 and T2 are using one of the snapshot isolation types so I expect those to use the version store. I assume with the database L being read only, it won't use the version store as there are no writes taking place.
So the two databases B2 and B3 are not using any snapshot isolation but appear to be using the version store. Why is this?
We can see msdb, and some user databases I have named T1, T2, B1, B2 and B3, are all using the version store at various points in this 24 hour period
If I run the following query against the server, it returns everything I would expect to use the version store
SELECT name,
is_read_committed_snapshot_on,
snapshot_isolation_state_desc,
is_read_only
FROM sys.databases
WHERE is_read_committed_snapshot_on = 1 OR
snapshot_isolation_state = 1The results are as follows:
B1, T1 and T2 are using one of the snapshot isolation types so I expect those to use the version store. I assume with the database L being read only, it won't use the version store as there are no writes taking place.
So the two databases B2 and B3 are not using any snapshot isolation but appear to be using the version store. Why is this?
Tagged:
Answers
So we can investigate this further, I need to request log file information from you. Therefore a support ticket has been created for you, the support ticket reference is #291119.
You will receive a message from our call ticket system regarding the reported problem, so you can reply with a copy of the requested log files.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
As discussed in the ticket raised this appears to be related to the functionality that Triggers use Row-level versioning and can take up space in the version store even if one of the associated isolation levels is not enabled (https://www.itprotoday.com/sql-server/triggers-and-version-store).
You mentioned this article https://techcommunity.microsoft.com/t5/sql-server-blog/managing-tempdb-in-sql-server-tempdb-basics-version-store-why-do/ba-p/383509 that shows this happened in SQL 2005.
Kind regards,
Alex
Have you visited our Help Center?