TempDB is Full. Who Did This?
TempDB is shared by every database on the instance. One badly written query — a massive sort, a spill to disk, a cursor nobody closed — can fill it up and take down everything else.
There are also two distinct problems here that look the same from the outside:
- Space exhaustion — a session is holding a huge temp table or sort spill
- Allocation contention — many small sessions hammering the same GAM/SGAM/PFS pages, causing waits
This query surfaces both.
What This Query Does
Part 1 — TempDB Usage by Session
sys.dm_db_session_space_usage tracks page allocations and deallocations per session inside TempDB.
The difference between allocated and deallocated pages is what’s currently held.
Two types of usage:
- User objects — temp tables, table variables
- Internal objects — sort spills, hash joins, cursors, row versioning
The query also shows the current statement being executed so you can see exactly what the culprit is doing right now.
Part 2 — TempDB File I/O Contention
sys.dm_io_virtual_file_stats shows I/O wait times per TempDB data file.
High stall on data files points to allocation page contention — the fix is adding more equally-sized TempDB data files (typically one per logical CPU core, up to 8).
Part 3 — TempDB File Configuration
Shows current TempDB file layout so you can spot unequal file sizes or too few data files.
TempDB Usage and Contention Script
Loading…
Gareth Winterman