How To Clear Tempdb Space In Sql Server

Every time someone shrinks a database, a kitten dies.

Stop shrinking your tempdb data files

I recently wrote about growing, shrinking, and removing tempdb files. In that article I explained that SQL Server won't move a page that contains an internal worktable object, and thus trying to shrink tempdb files can be futile. Today, I'm going to explain how attempting to shrink tempdb files can actually be harmful.

How's tempdb work again?

First, lets recall that when SQL Server starts, it will ensure that all datafiles are sized to match the metadata in sys.master_files. Let's take a look at what that metadata looks like for tempdb on my laptop. We'll look at both master.sys.master_files and tempdb.sys.database_files:

--Check sys.master_files in master USE master GO SELECT DataFileName = mf.name,         FileSizeMB  = mf.size*8/1024 FROM sys.master_files AS mf WHERE mf.database_id = db_id('tempdb') ORDER BY mf.type, file_id; GO --Check sys.database_files in tempdb USE tempdb GO SELECT DataFileName = dbf.name,         FileSizeMB  = dbf.size*8/1024 FROM sys.database_files AS dbf ORDER BY dbf.type, dbf.file_id;

I've got 8 data files, each 8MB, and one log file that is 12MB. Cool–that's a crummy configuration for a server, but just fine for this laptop. Also, we notice that it's the same in both places.

Next, I'll run some stuff on my laptop to generate a workload that will create some tempdb objects and force those data files to auto-grow. Let's re-run those same two queries above, and look at the results:

Notice how sys.master_files still shows the configured starting size, while sys.database_files shows the current size. For most databases, these two queries would return the same results. One of the exceptions is tempdb–where autogrowth is not tracked in sys.master_files. This is done to allow that metadata to remain constant so that SQL Server has the necessary information to "reset" tempdb to its configured size on restart.

Well, what if I don't want to wait for a restart? Maybe I can just shrink the data files a wee bit now, because I need some space back on the drive. That's OK to do, right?

I've frequently seen DBAs try to shrink tempdb files as a response to disk space alerts on the tempdb disk. In my recent blog post, I discussed how tempdb shrink operations often fail with an error like Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle. Let's put aside that complication, and assume that maybe I'm able to get the shrink to complete.

I really want to shrink some tempdb files!

What I've seen done (and a younger Andy did once upon a time) was think something like this:
"I just need a little space back to get below the alert threshold, so I try to shrink just a little bit of space off every file!"

USE tempdb DBCC SHRINKFILE (tempdev,50); DBCC SHRINKFILE (temp2,50); DBCC SHRINKFILE (temp3,50); DBCC SHRINKFILE (temp4,50); DBCC SHRINKFILE (temp5,50); DBCC SHRINKFILE (temp6,50); DBCC SHRINKFILE (temp7,50); DBCC SHRINKFILE (temp8,50);

Or maybe Younger Andy thought, "But I really want to get space back. What if I shrink just one file and I can shrink it really small!"

USE tempdb DBCC SHRINKFILE (temp2,5);

On the next reboot, it'll all resize back to the way it should be, right?

NO! Younger Andy, you just screwed up tempdb's metadata!

Remember these queries from the start of the blogs post? Let's run them again:

--Check sys.master_files in master USE master GO SELECT DataFileName = mf.name,         FileSizeMB  = mf.size*8/1024 FROM sys.master_files AS mf WHERE mf.database_id = db_id('tempdb') ORDER BY mf.type, file_id; GO --Check sys.database_files in tempdb USE tempdb GO SELECT DataFileName = dbf.name,         FileSizeMB  = dbf.size*8/1024 FROM sys.database_files AS dbf ORDER BY dbf.type, dbf.file_id;

I ran a SHRINKFILE to set every file to 50MB… then that one temp2 file to 5MB. Except, file sizes are all over the place. To make it worse, the "restart metadata" in sys.master_files has been reset to wonky sizes as well. The only file I didn't try to shrink is the log file–and that's the only file that didn't have the sys.master_files metadata mangled.

Any time you manually grow or shrink a tempdb file, the "restart metadata" in sys.master_files gets adjusted accordingly. If the data file can't shrink to the requested size, then the metadata is set to the size the data file was actually resized to, not the requested size.

Then what the heck are you supposed to do?

To start, just stop trying to shrink tempdb data files.

But how do you eliminate the need to shrink tempdb files?

Use a dedicated disk for tempdb.
Let the data files fill the tempdb disk.
Turn off your space alerting for that one disk, and be comfortable that the disk is 100% full.
Instead monitor the tempdb database itself for internal free space on log & data files.
Manage the size of your tempdb data & log files so that the initial size is large enough for normal operations.
If you do shrink a tempdb file, check the sys.master_files metadata before & after to ensure you leave it in the ideal state. Use ALTER DATABASE...MODIFY FILE to repair the metadata for the next restart