Recently I was working on performance issues for a client and one of the topics we were looking at was tempdb. There was some confusion about when tempdb is a problem so I figured I would write this quick post explaining the auto growth and shrink behavior of tempdb.
Here is what I started with.
SQL 2012 developer edition instance with 4 tempdb files (1 per core) on a system with 8 GB RAM and Intel i5 processor.
The query to find space used for each data file:
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
Now I insert some data into a temp table using the below script:
create table #tempdata
( data varchar(8000)
)
insert into #tempdata
select replicate('a', 8000)
insert into #tempdata
select *from #tempdata –- execute until tempdb grows
The files grew from an original file size of 8 MB to up to 97 MB. I disconnect my session for the query and notice how (in screenshot below) the free space is now around 94 MB per file. However this space is not released back to the OS.
 |  |
Now I run the query once again:
Notice how additional filesize or autogrowth is not requested by SQL. This is why it is recommended to pre size the tempdb so that SQL doesn’t need to bother the OS to allocate space again and again. The problem is it’s tricky to get to size right the first time, so either try trial and error or monitor for a decent time period and then set the size.
Most people are familiar with the behavior till here.
Now when I run Shrinkfile as shown below
Notice that the file sizes haven’t changed since I still have the session active. This is why you need to look for long running queries before you take action on the tempdb. Sometimes it’s just one query that is the cause and killing it should be enough.
Here is where it gets interesting, what if I have a query that is critical and I can’t kill it but at the same time I am running out of space?
| DBCC SHRINKFILE (N’tempdev1′ ,0, TRUNCATEONLY) | DBCC SHRINKFILE (N’tempdev1′ ,8) |
 |  |
If you notice the above screenshots you will see the comparison between release unused free space vs reorganize the file.
When you use the first option it doesn’t release the space back to OS in fact it doesn’t make any changes until the session using the tempdb has terminated which means you have to kill the long running query in order to get space back. Else you can simply resize the individual files as shown in the second screenshot. In this case what SQL does is reorganize the pages so that they move to the top of the data file and then it is able to release the extents back to OS from the tail of the data file. This way you are able to reclaim the free space back to the OS without having to tamper with the queries.
Please keep in mind that at some point you will run out of space in tempdb if the query is still not done. But with the second approach you have more room to maneuver.
A more complete picture is shown below where you can clearly see that running reorganize did free up space but release unused space unlike what its name suggest doesn’t actually release unused space until the session that started the temp table creation is done.
Quick note on the reorganize files option for Shrink File: The file size you set here becomes the new initial file size for the data file. So this approach does have a drawback where the size of tempdb fluctuates drastically. In some cases the new file size default might still be large than the potential minimum file size.
Please Consider Subscribing
