Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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).

share|improve this answer
    
Thank you for that insight. I can't seem to get the whitepaper following your link. –  Preet Sangha Jan 23 '13 at 22:32
    
I've updated the whitepaper link directly to the vendor site. –  Mike Fal Jan 23 '13 at 22:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.