In my previous post I discussed a particular query design that made the tempdb data files consume all available drive space. When discussing the resolution I noted one of the steps was to reduce the size of the data files. That’s means shrinking them, and I am fully aware that sometimes tempdb can stubbornly refuse to shrink. It’s kind of the reverse problem George Costanza mentioned about swimming.

So let’s talk about what you can do when tempdb requires shrinkage.

First though, I’d like to start with a disclaimer. Most DBA professional types would say shrinking tempdb just for the sake of shrinking it is a bad idea. If your tempdb keeps growing as a result of general use then you should probably just give it that room. Let it breathe, man!

That said, sometimes a particular anomaly may cause the tempdb to grow abnormally. This post is about those cases, where you want to return the data files to a predetermined size. And I don’t mean 8 megabytes.

So here is your trusty “shrink my data file to this size” command.

DBCC SHRINKFILE (tempdev, 1024);
GO

This command will try to shrink your default tempdb file to 1024 megabytes, also known as 1 gigabyte. I’ve executed it a few times in my day. Not so much that we’re old friends, but we’re definitely acquaintances.

But this is a post about the times this command doesn’t work, the times your tempdb data files grew 10 or 100 or whatever extra gigabytes and now you have to clean up the mess. And when you execute the command above you notice that the data files don’t shrink. In a fit of panic or frustration you got out your Google and somehow you ended up here.

Relax. I’m 99% sure I can help. Here is a list of commands to try before shrinking, in order of precedence due to least impact (read: danger) to your instance.

USE tempdb
GO
CHECKPOINT;
GO

What does it do?

It generates a manual checkpoint for the tempdb database, which means creating a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash. That’s a good thing.

Is this dangerous?

Not at all. It might not work and throw an error, but that’s likely because there is an open transaction still requiring pages in tempdb, like the situation I had in the previous post. You need to CHECKPOINT tempdb in order to shrink it without having corruption issues. As you can imagine, corruption issues are very, very bad. For your database, it’s like “Thanos got all of the Infinity stones” level of bad. Data pages just vanish.

USE tempdb
GO
DBCC SHRINKFILE (N'<tempdb filename>', TRUNCATEONLY) ;
GO

What does it do?

It releases space at the end of the tempdb data file, but it doesn’t move the pages in the file around. This can often provide quick releif so long as the pages at the end of the file aren’t in use. If they are…well, nothing will happen.

Is this dangerous?

Not very, but as I said it only releases pages from the end to the last used extent in the data file, so if you jsut filled up all the files it might not provide relief.

DBCC FREESESSIONCACHE;
GO

What does it do?

This flushes the distributed query connection cache used by distributed queries.

Is this dangerous?

Probably not. Do you even have any distributed queries? Those are queries between servers, so unless you have set up endpoints or linked servers you probably don’t. Which also means that although this command won’t hurt you, it probably won’t help you shrink tempdb either.

DBCC FREEPROCCACHE;
GO

What does it do?

Removes all elements from the plan cache. Those elements would be execution plans used by queries.

Is this dangerous?

Doing this is more disruptive than dangerous, but this certainly is not something you want to do without cause. When you execute this command all of your plan cache gets discarded from memory like everything you knew about that Emily Bronte book you had to read in high school. game. They plans are all gone, all of them, which means every time you fire up a new query it’s going to have to take a millisecond or two (or a thousand) to recompile a new execution plan.

DBCC FREESYSTEMCACHE ('ALL');
GO

What does it do?

Releases all unused cache entries from all caches. Did you know there are other caches? Go query distinct names in the sys.dm_os_memory_clerks table and see how many hundreds of other caches your instance has.

Is this dangerous?

Like freeing the procedure cache in the previous step, this will cause some (hopefully) invisible delays for various processes that are caching data. If you want you can try to be like a surgeon by replacing ‘ALL’ with particular system cache allocations like ‘tempdb’ and ‘Temporary Tables & Table Variables’, but if you’ve already embraced freeing the procedure cache then use ‘ALL’ to cover any possible objects still in tempdb.

DBCC DROPCLEANBUFFERS;
GO

What does it do?

Removes all clean buffers from the buffer pool. That sounds innocuous, right? Removing buffers. But what are clean buffers? Just all of your data pages in memory that have been flushed to disk.

Is this dangerous?

Most definitely. When you have a query, for performance reasons the database engine looks first in the buffer pool for pages to be read. These are known as logical reads, and are generally much faster because they are in memory and require much less interaction with things like storage and maybe even networks. Dropping the clean buffers means you don’t get to do that. Your queries now have to all go read pages off disks, and you will probably get “the database is slow” tickets filling up your Inbox until heavily used data is back in the buffer cache again. Do not do this unless all previous steps have not worked. I’m super serious.

One final option

If you find you can only shrink to a certain size no matter what you do, or you get a message that says something like “File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty”, then it can often be helpful to grow the data file a few MB. Yes, that sounds counterintuitive, but often when the very last pages are in use, SQL Server is unable or unwilling to move data files around. If you give it a little breathing room, your next attempt to shrink might be successful.

Now, after executing some or all even all of these commands you should be able to shrink the tempdb data files to the desired size. However, if you’ve gone this far and still none of this works, well, I’m sorry. Your only option left is to break out the Holy Hand Grenade of Antioch and just restart the service.