Size of TempDB Microsoft SQL Server 2005

Question

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full.

I am creating large temporary tables in a couple of queries. Once I am finished running queries, I drop the temporary tables. However, tempdb remains relatively large. Should I be worried about any subsequent problems with the server? Such as performance, or something I am not able to think of.

Answer 1

You should grow the initial size of tempdb so that those large temp tables no longer cause auto-growth, since it sounds like that is your normal load. This way you reserve the disk space that is required by your application. You do not want to run into the nasty surprise that temdb cannot grow and causes failures in your app. See Capacity Planning for tempdb and Optimizing tempdb Performance:

Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

Answer 2

Tempdb will be recreated if the server is restarted and at that time tempdb will be reset size wise to initial size settings. In the meantime once the temporary tables are dropped space will free in the data files but size on disk will remain the same.

I wouldn't however worry about the size unless you have space issues. Next time you run queries that use tempdb a lot you will save on growth operations and get a bit of a boost to performance since it will use the above mentioned free space in the data files