Just a day ago, while installing SQL Server on our development machine Jr. DBA asked me what should be kept file growth of the TempDB. I really have not thought about this till moment and I looked at MS site.
I found following answer and I think it is quite interesting.
tempdb file size – FILEGROWTH increment for tempdb
0 to 100 MB – 10 MB
100 to 200 MB – 20 MB
500 or more – 10%
Initially you can not know what will be the final size of your TempDB, but as you continue using server for a while and can detect what is the size of the TempDB. Based on your size of TempDB ideal size of FILEGROWTH can be determined.
Reference : Pinal Dave (https://blog.sqlauthority.com), MS – TempDB
3 Comments. Leave new
when you add files to tempdb (based on the number of CPUs)
are we expected to see the default file tempdb.mdf to growth evenly with the others tempdb files added ?
thanks,
dbaexp2008
Oh yes. If not tempdb load balancing goes off the rails. It uses a simple algorithm that looks at the largest file to determine which file to allocate work to next. Files need to be the same size with same growth rate to facilitate even work load. Use trace flag 1117, e.g. DBCC TRACEON (1117,-1)
Can we have a HOW-TO deal with TempDB? the step by step guide? I’m sure that would be great coz I see nothing like that esp MS Sites.
thank you in advance
Raleigh