Understanding the usage of version store in tempdb We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4.

I know tempdb would need the space depending upon the transactions, and how queries are designed to use it.

We had an issue for disk space getting full , where we analyzed how tempdb grew that big over a week.

After a reboot, via monitoring we came across 4-5 queries, which were holding up version store for more than 20 minutes, grew tempdb same day from its initial size of 2 GB ( 4 data files) to over 100 GB within a spam of 6-8 hours.

For example 

query A holds the version store for 10 minutes where current version generation rate was 23.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 17 MB and tempdb file size was 10332 MB.

Later after an hour the query B ran, the version store for 16 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 45 MB and tempdb file size was 45332MB.

Similarly a query C ran after 3-4 hours, the version store for 15 minutes where current version generation rate was 33.81 KB/sec and the cleanup rate was 0.00 KB/sec.  The version store size was 19 MB and tempdb file size was 85332MB.

and so on on. during our monitoring for that day.

Now same queries run next day and day after,

We see that tempdb allocations and dellocations value running to somewhat similar during the very first day, but tempdb did not grew that fast.

same happens for upcoming 7-8 days where growth is approx 8-10 GB compared to first day where it grew to 100 GB for the similar queries running each day.

Also , the current output of select  count(*) from sys.dm_tran_version_store

is 196470 and its been 2 days since a reboot.

May be i am unaware of some process here, but please help me understand what could be the reason apart from those identified queries as we are trying to improve if they are the one causing tempdb to grow that much.

Note* tempdb size is 110 GB and Free space it shows is almost 109 GB.
There can be many reasons for that.


You're queries run and generate a version, during another query use the same tables. During this phase version 1 will be hold back. If your transaction isn't commited at the end, it may occur that version 1 will last for a long time in your tempdb. Make sure that all transactions are probably commited and closed.
Make sure that all information is stored on the disc. It may occur that your information is still stored in your memory. This could cause the hold of a version in your tempdb. You can force the latch process by using CHECKPOINT after your commit.


Hopefully this will help you.