SQL Server problems
tempdb Is Full: Which of Three Things Uses It, and Which Session
22 November 2026
Every database of an instance shares one tempdb, so when it fills, statements fail everywhere at once. Three things use it: objects that sessions create, work space that queries spill into, and the version store. One query tells which of the three it is, and the next names the session.
What the error says
Msg 1105: Could not allocate space for object 'dbo.SORT temporary run storage' in database 'tempdb' because the 'PRIMARY' filegroup is full. The data files of tempdb are full and cannot grow. Msg 9002 for tempdb is its transaction log, held by a long transaction. Msg 3967 means the version store had to shrink and a snapshot reader lost its versions.
The object in the message is a first hint. SORT temporary run storage or a worktable is a query that spilled. A name that starts with # is a temp table.
Which of the three uses it
SELECT SUM(user_object_reserved_page_count) / 128
AS user_objects_mb,
SUM(internal_object_reserved_page_count) / 128
AS internal_objects_mb,
SUM(version_store_reserved_page_count) / 128
AS version_store_mb,
SUM(unallocated_extent_page_count) / 128
AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;
| Largest part | What it is | Who to look for |
|---|---|---|
| User objects | Temp tables, table variables, and tables created in tempdb. | A session that loads a large #table or never drops it. |
| Internal objects | Sorts, hash joins and spools that did not fit in memory, and cursors. | One query with a poor plan or a memory grant that was too small. |
| Version store | Old row versions, kept for snapshot isolation, read committed snapshot, triggers and online index builds. | The oldest open transaction: versions stay until it ends. |
Which session
For user and internal objects, the requests that run now and what they have taken:
SELECT TOP (10) x.session_id, x.user_mb, x.internal_mb,
s.login_name, s.program_name,
SUBSTRING(t.text, 1, 60) AS sql_text
FROM (SELECT tu.session_id,
SUM(tu.user_objects_alloc_page_count
- tu.user_objects_dealloc_page_count) / 128
AS user_mb,
SUM(tu.internal_objects_alloc_page_count
- tu.internal_objects_dealloc_page_count) / 128
AS internal_mb
FROM sys.dm_db_task_space_usage tu
WHERE tu.database_id = 2
GROUP BY tu.session_id) x
JOIN sys.dm_exec_sessions s ON s.session_id = x.session_id
LEFT JOIN sys.dm_exec_requests r
ON r.session_id = x.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE x.user_mb + x.internal_mb > 0
ORDER BY x.user_mb + x.internal_mb DESC;
A session that is idle but still holds temp tables shows in sys.dm_db_session_space_usage, with the same columns per session. For the version store, the transaction that keeps the versions:
SELECT TOP (5) a.session_id, a.elapsed_time_seconds,
s.login_name, s.host_name, s.program_name
FROM sys.dm_tran_active_snapshot_database_transactions a
JOIN sys.dm_exec_sessions s ON s.session_id = a.session_id
ORDER BY a.elapsed_time_seconds DESC;
A version store that only grows always has one old transaction under it, often a session that opened a transaction and went idle.
The fix, in order
- End what fills it. Stop the query that spills, or end the transaction that holds the version store.
KILL 57;ends a session, and its space is returned once the rollback is over. - Give tempdb room if the volume has it.
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 32GB);Grow every data file to the same size: SQL Server fills them in proportion to their free space, and equal files keep allocation even. - Add a file on another volume when the first one is full.
ALTER DATABASE tempdb ADD FILE (NAME = tempdev9, FILENAME = N'T:\tempdb\tempdev9.ndf', SIZE = 16GB);It works at once, with no restart. - Fix the query, not the size. A sort or a hash that spills gigabytes has a plan problem: a missing index, an estimate that is far off, or a memory grant cut by concurrency. The plan shows the spill as a warning on the operator.
- Size tempdb for its busiest hour. A tempdb that starts small and grows in steps stalls every session on each growth. Set the files to the size the peak needs, with a fixed growth step in megabytes.
- Keep the restart for last. A restart recreates tempdb at its configured size and ends every session of the instance. It also removes the evidence of what filled it.
See it coming
tempdb fills in minutes when a query spills and over hours when a transaction holds the version store. Radar Engine, the detection layer of Scops, follows both, with no agent on the server:
| Detector | When it fires |
|---|---|
| tempdb fill | Measured against what tempdb can reach: the limits of its files and the free space of their volumes. Warning: 70% used, climbing, and full within 3 hours at that rate. Warning too: the version store is at least half of the used space and has grown for 60 minutes. Critical: full within 30 minutes, or 95% used. |
| One statement taking over | A single statement takes most of the instance's activity. |
| Memory pressure | Queries wait for a memory grant, which is when sorts and hashes start to spill. |
A temp table that appears for a minute does not count as a climb, and a tempdb that sits on a plateau stays quiet. The Dashboard's Tempdb tab shows the used space by type over time and the sessions that use it now.
Questions
Does restarting SQL Server clear tempdb?
Yes. tempdb is created again at every start, at its configured size. A restart ends every session of the instance and removes the evidence, so find the session first.
Can I shrink tempdb while the instance runs?
DBCC SHRINKFILE works on tempdb files, but only frees space that nothing uses, and it can block against active sessions. End what fills tempdb first, then shrink only if the volume needs the space back.
How many tempdb data files should there be?
Microsoft's guidance is one per logical processor up to eight, all of the same size, and more only if allocation contention remains.
Why does the version store keep growing?
Row versions can be removed only when no transaction can need them any more. One transaction that stays open, even an idle one, keeps every version made since it began.
Related problems
- Error 9002: the transaction log is full
- SQL Server blocking: find the head blocker
- Error 1205: read the deadlock graph
- All SQL Server problems
- SQL Server monitoring without agents