tempdb growth and shrinking tempdb

Question

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?

score 7 · Answer 1 · edited Apr 13 '17 at 12:42

7

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.

edited Apr 13 '17 at 12:42

Community

1

answered Apr 15 '16 at 15:21

Hannah Vernon

70,928
22
177
323

score 2 · Answer 2 · edited Apr 18 '16 at 07:02

2

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.

edited Apr 18 '16 at 07:02

Paul White

94,921
30
437
687

answered Apr 15 '16 at 15:24

James Anderson

5,794
2
27
43

score 2 · Answer 3 · edited Apr 15 '16 at 16:03

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.

score 1 · Answer 4 · answered Apr 15 '16 at 15:49

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.

score 0 · Answer 5 · edited Jan 12 '20 at 11:32

Occupied space won't be released until SQL services restart/ shrink operation even if free space inside available too. Inside free space would be used for upcoming transaction and not released for OS.

For better performance and equal space utilization create tempdb data files as per your number of CPU core. If you have 4 core then create 4 data files in tempdb with equal size.

To avoid space issue make separate drive for tempdb files then no of I/O latency reduced with user database.

tempdb growth and shrinking tempdb

5 Answers5