Shrinking tempdb without restarting SQL Server

Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.

Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.

Tempdb stores temporary tables as well as a lot of temporary (cached) information used to speed up queries and stored procedures. For the best chances in shrinking tempdb, we’re going to clear these different caches (except for the temp tables, which you should drop manually).

First off, the easy way out

It’s worth mentioning. If you’re not running a production-like environment, your best bet is to restart the SQL Server service. This will return tempdb to its default size, and you won’t have to worry about all the potential pitfalls of this article. But since you’re reading this, chances are you can’t just restart the server. So here goes:

Warning: These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely neccessary.

DBCC DROPCLEANBUFFERS

Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

DBCC FREEPROCCACHE

Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.

DBCC FREEPROCCACHE;
GO

DBCC FREESYSTEMCACHE

This operation is similar to FREEPROCCACHE, except it affects other types of caches.

DBCC FREESYSTEMCACHE ('ALL');
GO

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.

DBCC FREESESSIONCACHE;
GO

.. and finally, DBCC SHRINKFILE

DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.

Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!

DBCC SHRINKFILE (TEMPDEV, 20480);   --- New file size in MB
GO

Don’t set the new size too low! Make a realistic estimate of the largest “normal” size that tempdb will assume during normal day-to-day operation.

That’s it. If everything works the way it should, you should now be able to verify the new size of tempdb.

A word about shrinking database files

Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation on your physical storage medium. This is because the sectors that the file used to occupy may now very well be occupied by other information (just a few bytes are enough). When SQL Server wants to grow that database file, the newly added portion of the file will need to be placed elsewhere on the disk, thus creating fragmentation.

The number one mortal sin in this context is “autoshrink“, because it may very well add to the drive fragmentation every time it runs, which could be very frequently.

As a rule of thumb, never ever autoshrink a database. And try to be very restrictive when it comes to shrinking databases or files in general, unless it’s a one-off operation to fix the aftermath of a runaway query.