Introduction

In SQL Server, the tempdb system database can grow significantly due to heavy workloads, temporary objects, or large queries. Sometimes, even after reducing workload or removing temporary data, tempdb does not shrink automatically. Manual intervention is required to reclaim the space. This document outlines the step-by-step process to attempt shrinking tempdb when standard shrinking methods fail.

⚠️ Warning: These steps may remove all caches from SQL Server, potentially affecting performance. Avoid using them unless absolutely necessary and always scope operations to the database level whenever possible.

Step-by-Step Guide

Step 1: Issue a Checkpoint

CHECKPOINT;

Step 2: Attempt Shrink

DBCC SHRINKFILE(tempdev, TARGET_SIZE);

Step 3: Check If Shrink Was Successful

If the file shrinks, the process is complete.
– ✅ If YES → DONE
– ❌ If NO → Proceed to Step 4

Step 4: Clear Buffers

DBCC DROPCLEANBUFFERS;

Step 5: Free Procedure Cache

DBCC FREEPROCCACHE;

Step 6: Attempt Shrink Again

DBCC SHRINKFILE (tempdev, TARGET_SIZE);

Step 7: Check If Shrink Was Successful

If the file shrinks, you’re done.
– ✅ If YES → DONE
– ❌ If NO → Proceed to Step 8

Step 8: Clear Session Cache

DBCC FREESSESSIONCACHE;

Step 9: Clear System Cache

DBCC FREESYSTEMCACHE(‘ALL’);

Step 10: Final Shrink Attempt

DBCC SHRINKFILE(tempdev, TARGET_SIZE);

Conclusion

If tempdb does not shrink using standard commands, follow this escalated sequence of clearing memory and cache objects cautiously. These operations impact the server’s performance by clearing critical internal caches. Only use them during low-usage periods or in maintenance windows and always test in lower environments before applying in production.