[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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 ?

1 Solution
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.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now