SQL Server TempDB Full: Fix It Without Restarting

Quick answer: Check sys.dm_db_file_space_usage for space used by query plans vs. internal objects, then increase TempDB file size manually or kill the blocking query with DBCC DROPCLEANBUFFERS won't help — you need to release version store or internal object allocations.

I've walked into a dozen small businesses where the entire accounting system went dark because TempDB filled up. The error shows up as 'Could not allocate space for object in database TempDB' — and users are stuck, report queries fail, SSMS throws errors. Most folks panic and restart SQL Server. Don't. That's the sledgehammer approach. You can fix this live, without dropping connections, as long as you act fast.

TempDB fills for three reasons: 1) a runaway query (like an unoptimized SELECT INTO with a hash join), 2) too-small initial size with slow auto-growth, or 3) version store buildup from long-running transactions in databases using READ COMMITTED SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION. Last month, a client had a stored procedure that built a 50GB temp table because of a missing join filter. TempDB hit 10GB default and choked.

Step 1: Diagnose What's Eating TempDB

1. Open SSMS and run this query:

SELECT 
  SUM(unallocated_extent_page_count) AS free_pages,
  SUM(internal_object_reserved_page_count) AS internal_reserved,
  SUM(version_store_reserved_page_count) AS version_store,
  SUM(user_object_reserved_page_count) AS user_objects
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;

Multiply by 8KB to get MB. If version_store is huge (say, >20% of TempDB), you've got a long-running transaction in snapshot isolation. If internal_reserved is big, it's usually a hash join or sort spilling to disk.

2. Find the culprit query:

SELECT 
  session_id, 
  command,
  wait_type,
  last_wait_type,
  host_name,
  login_name,
  program_name
FROM sys.dm_exec_requests
WHERE database_id = 2;

Look for sessions with command like 'SELECT INTO', 'INSERT', or 'SORT'. If you see a wait_type of PAGELATCH_EX or PAGELATCH_UP on TempDB, that's contention. Note the session_id.

3. Check what the session is running:

SELECT 
  er.session_id,
  t.text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) t
WHERE er.session_id = <your_session_id>;

Step 2: Free Up Space — Don't Shrink TempDB

TempDB file shrink doesn't help much — it releases space at file level but doesn't free internal allocations. Instead:

• If the version store is big, kill the transaction holding it: Identify the transaction using sys.dm_tran_active_snapshot_database_transactions and check transaction_id. Then run KILL <session_id> for the blocking session. Version store will clear as tempdb checkpoints.
• If a user object or internal object is huge, kill that session. Yes, that query will fail, but the space releases immediately.

Had a client once where a developer ran SELECT * INTO #temp FROM huge_table without a filter. Killed it, TempDB dropped from 95% to 20% in 30 seconds.

Step 3: Grow TempDB Manually (Fastest Relief)

If you can't kill the query (maybe it's critical), add space manually:

USE master;
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 20GB);  -- Adjust size

For multiple data files (you should have them on SQL Server 2016+), repeat for each file. This takes effect immediately, no restart. But if the drive is full, this won't help.

Alternative Fixes

Add a New TempDB Data File

Sometimes auto-growth is too slow. Add another file on a different disk if possible:

ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\Data\tempdb2.ndf', SIZE = 5GB, FILEGROWTH = 1GB);

Check for Non-Yielding Scheduler

If TempDB is full and SQL Server is unresponsive, you might have a scheduler bug. Check the SQL Server error log for 'Non-yielding scheduler' messages. If you see that, you'll likely need to restart — but try adding files first, as that sometimes breaks the deadlock.

Prevention Tips

• Set TempDB to 8 files (or equal to number of logical cores, capped at 8). Starting with SQL Server 2016, TempDB auto-configures to 8 files. Stick with that. Less contention.
• Set initial size to 10GB or more. Don't rely on auto-growth. On a server with 64GB RAM, I set TempDB to 20GB initial per file. Overkill? Maybe. But it never fills up.
• Enable instant file initialization. This lets TempDB grow fast without zeroing pages. Add the SQL Server service account to the 'Perform Volume Maintenance Tasks' local policy.
• Monitor with a simple alert: Set up a SQL Agent job that checks sys.dm_db_file_space_usage and emails you when free space drops below 20%.
• Optimize queries that spill to disk. Use SET STATISTICS IO ON and look for 'Worktable' reads. That's a sign of sort or hash spilling to TempDB. Add indexes or rewrite the query.

One more thing: never, ever shrink TempDB as a maintenance plan. Shrinking fragments files and causes performance issues. I've seen DBAs run shrink jobs nightly and wonder why TempDB has page latch contention. Stop. Set the size right once.