5

I’m a DBA that is fairly new to SQL Server coming over from a UNIX/Informix.

I have a question about tempdb growth:

From what I can see on regular databases if I start with a database that is 10 GB and then put in a bunch of data into it to make it 100 GB, and then delete 50 GB out of it I have to perform a shrink to reclaim space on the hard drive. Until I do that the space is allocated to the database

How does the tempdb work?

If an initial size is 10 GB and then some query grows tempdb to let’s say 100 GB and then terminates does the space become available for future tempdb queries/temp tables etc..?

Or does it release the space to the operating system?

6

Tempdb is recreated whenever the instance is restarted. At startup tempdb is created at the size specified in the system catalog. This means if you define tempdb to be 10GB, and it grows over time to 100GB, then you restart the server, tempdb will now be 10GB again.

The question about shrinking is one loaded with bad connotations. For a discussion around that, refer to this question.

1

How does the tempdb work? If an initial size is 10 GB and then query grows tempdb to let’s say 100 GB and then terminates does the space become available for future tempdb queries/temp tables etc..? Or does it release the space to the operating system?

SQL Server does not release space back to the operating system unless you tell it to release or restart the system. If your query grows the tempDB to 100GB, it will remain at 100 GB and the space can be used by other queries.

1

After growth the space will be available to future queries. The size will reset after a restart but file growths are expensive so if you have queries that are causing your TempDB to grow then you should really look at manually growing it out to match your requirements.

Data file growths can be made considerably less expensive, though still not free, if Instant File Initialization is used.

0

You have some good answers

I had to look it up and turns out you can shrink tempdb

Yes the the space is used by other queries

When you restart the server it will start at the initial size.
I don't know if under the covers it shrinks of just allocates fresh.

Growing the tempdb is somewhat expensive so unless you really need the space back then leave it. But you might have a massive operation that grows tempdb and you are not likely to do anything that massive for a while. You can shrink with the temp active. Or if you can restart the DB then tempdb is reset.

As warned by other shrinking a regular DB has down sides so unless it is space you really need back the leave it with free space.

Since you are new to tempdb take a look at this Optimizing tempdb Performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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