How to Shrink TempDB in SQL Server Without a Restart

Introduction

TempDB can balloon over time — especially in systems that run heavy queries, use version store, or rely on temp tables. Restarting SQL Server reclaims TempDB space, but that’s rarely an option in production. In this post, I’ll show you how to shrink TempDB without restarting the instance, and share the exact script I’ve used on real-world servers.

Why Shrinking TempDB Isn’t Straightforward

• TempDB uses system-versioned objects, version store, worktables
• It’s shared across all sessions — hard to isolate
• Many DBAs try shrinking but see “no space freed”

Safe Steps to Shrink TempDB Without Downtime

1. Identify active TempDB usage
   • SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50;
2. Kill idle sessions if possible (carefully!)
   • —Example only — use wisely
   • KILL 53;
3. Run Shrink File on each TempDB file
   • DBCC SHRINKFILE(tempdev, 5120); -- 5GB
   • DBCC SHRINKFILE(temp2, 2048);
   • -- If you have multiple files
4. Check result
   • EXEC sp_helpfile;

Best Practices

• Don’t shrink too small — TempDB will auto-grow again
• Always pre-size TempDB to avoid repeated growth/shrink cycles
• Monitor sys.dm_db_file_space_usage for growth patterns

Bonus: Script to Automate Safe Shrink

---

-- Custom logic to shrink only when usage is low
USE tempdb;
DECLARE @UsedMB INT;

SELECT @UsedMB = SUM(unallocated_extent_page_count)/128
FROM sys.dm_db_file_space_usage;

IF @UsedMB < 5000  -- only shrink if less than 5GB used
BEGIN
    DBCC SHRINKFILE(tempdev, 5120);  -- shrink to 5GB
END

Summary

Shrinking TempDB without a restart is possible — but only if you check active usage, kill idle sessions, and size it smartly. Use this only when space pressure is critical. Otherwise, set a good fixed size and leave it alone.