Size of TempDB

What should I set for the size of TemDB database and log file ? Will it be too small for default size of 8MB ? How to determine the size on SQL 2008 R2 server ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AnujSQL Server DBACommented:
tempdb is mainly used for the following :

Temporary user objects like #temp table and @table variables
Internal objects  like work tables,intermediate results, MARS, work file, sorting  etc
Version store, snapshot isolation level and change tracking
Service Broker, database mail.
DBCC CHECKDB, Online Indexing etc.

8 MB is too small, however there is no good number for this but there should be enough size to avoid constant tempdb expansion, the initial tempdb size depends on your workload and whether you are using any of the above, Start with an average size then monitor the file size growth for a week or two then get then do some math to get the good number and resize the tempd so that tempdb growth is not triggered frequently.

There is no issues of being  tempdb too large until you are running out of free space either in tempdb or the drive, but make sure that instant file initialization is on for the sql server service account, this helps to reduce the file expansion time in the event of auto-growth. Another import thing is the number of files of tempb general recommendation is to have .5 to 1 files per cpu cores to reduce tempdb contention and disk band width, make the size of the files equal to get the benefit of proportional fill.
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no a standard size for tempdb but I can let you some advices based in my experience:
1) - tempdb datafiles and logfile should be in a separate disk
2) - create at least 1 datafile per server core. If your server have more than 8 cores, you can stop at 8 datafiles and check the performance and if it's needed add 2 datafiles at a time and keep checking the performance until adding more datafiles doesn't boost more the performance.
3) - all datafiles should have the same size so will help SQL Server engine algorithm for balancing the data
4) - because tempdb it's used for some table operations, it's minimum size should be at least equal to the size of the largest table in the SQL Server instance.

So, I think the point 4) can help you to define the size of the tempdb but I wanted to let you some more advices.

Hope this can help you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.