Link to home
Start Free TrialLog in
Avatar of AXISHK
AXISHK

asked on

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 ?

Tks
Avatar of Anuj
Anuj
Flag of India image

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.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial