Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.