SQL SERVER – Ideal TempDB FileGrowth Value

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

SQL Server, SQL TempDB
Previous Post
SQL SERVER – Find Table in Every Database of SQL Server – Part 3
Next Post
SQLAuthority News – SQL SERVER Database Administrator Job Description

Related Posts

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 ?


    • ginaimlachMarkA
      September 23, 2019 7:58 am

      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


Leave a ReplyCancel reply

Exit mobile version