Sql-server – Size of TempDB Microsoft SQL Server 2005

sql serversql-server-2005tempdbtemporary-tables

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.

Best Answer

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.