When Shrinking Temp DB Just Won’t Shrink

This is broken. You run DBCC SHRINKFILE on the Temp DB data files, maybe throw in a CHECKPOINT for good measure, and nothing moves. The files sit there stubbornly taking up space you need back.

I am not in the habit of shrinking databases. It fragments files and usually just creates more work later. But sometimes you have no real choice, and it’s actually in your best interest to be shrinking the free space within some database.

A bad ad-hoc query can sometimes fills up you database data or log files. It might cause your disk alerts fire, or storage won’t extend the drive they’re tight on space themselves.. You need the space cleared now as the DBA, so what do we do?

Why It Used to Be Worse

It used to be that shrinking Temp DB came with real warnings about possible corruption, so the safe move was always a service restart. Paul Randal has since shown that’s no longer the case on current versions.

Even so, the shrink often refuses to cooperate. You try shrink a database file and it either takes an endless amount of time, nothing is happening. Or, the shrink operation completes but nothing actually shrank.

The Fix That Usually Works

Clear the procedure cache as a first attempt at troubleshooting this:

DBCC FREEPROCCACHE;

Once you’ve run this, try the shrink again. This releases internal objects tied to cached plans that were holding onto space in Temp DB.

I hit this exact situation once with eight data files. Repeated shrinks and checkpoints did nothing. I was ready to defer it to the next patching window. Then I tried FREEPROCCACHE and the files finally gave ground.

When the Log Is the Problem

Sometimes the data files shrink but the log stays full or unshrinkable for other reasons.

• Stuck CDC: Change Data Capture can lag or get blocked. The capture and cleanup processes hold log records that prevent truncation. Check the CDC jobs and status.
• Replication: Transactional replication or similar features create persistent activity that keeps the log active. Review Replication Monitor for latency or errors.
• Active transactions: Run DBCC OPENTRAN('temp db') to spot anything lingering. Internal objects and version store usage from snapshot isolation or heavy query spills can also pin things down.

In those cases, try this sequence:

USE temp db;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE('ALL');
GO

Then run your SHRINKFILE commands again.

What to Do in Production

Only shrink when you truly have to. Focus on the root cause—tune the offending queries, fix CDC/replication backlogs, and size Temp DB properly from the start with multiple equally sized data files and sensible growth settings.

I tested these steps in real environments where alerts were live. They work without needing a restart in most cases.

Have you seen other reasons Temp DB refuses to shrink? Drop the details below!