Should a SQL Server TempDB space be allowed to be reclaimed?

Question

If I don't have space concerns at the moment, should I size my TempDB as a large space initially and not let it's space be reclaimed?

What's the best way determine what the optimal initial size for my TempDB should be?

My TempDB is currently 12gb and it's set to 10% autogrowth

Answer 1

TempDB (as with any other files) should be sized to a stable amount appropriate to your instance so that it shouldn't have to grow. The idea is to minimize autogrowth events, because any time your files have to grow, your queries will be forced to wait until the file growth has been completed. My practice(and this is my practice, your miles may vary) for sizing TempDB is as follows:

• Total TempDB data file size should be approximately 1/3rd my total data file size for all databases
• Number of data files is half the number of logical cores up to 8 total data files.
• Log file is sized to 1/3rd total size of TempDB data files
• I place TempDB on a LUN where it will have some space to grow, so that TempDB will have some ability to grow if a process requires more TempDB than I allocated.
• I set explicit values for TempDB file growth, typically 1GB (depends on file size). I do not use percentages for growth on any files, as percentage growth is harder to predict and accommodate.

As for giving space back to the OS, I typically don't do this. The only time I will give space back is if there was an obvious run away process that blew TempDB out to an abnormal size. To give the space back, I restart SQL in order to recreate TempDB fresh (as TempDB is created fresh on every SQL Service start) since shrinking TempDB can cause corruption.

There is a lot of discussion and opinion on exactly how TempDB should be configured. Again, this is my approach. For more on TempDB best practices and approaches, I recommend this whitepaper from Robert Davis(it's a vendor site, so you'll have to register).

Answer 2

TempDB will grow as needed. For a new server I will usually pregrow to about 8GB and then watch it to see what size it settles at. Then you can set that as the initial size. You'll also want to change the default autogrowth settings. Otherwise you'll get 1mb growth increments which is far less than optimal.

Sizing tempDB really depends on your workload. What do your queries need, how much data do they process.