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 partWhat it isWho to look for
User objectsTemp tables, table variables, and tables created in tempdb.A session that loads a large #table or never drops it.
Internal objectsSorts, 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 storeOld 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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:

DetectorWhen it fires
tempdb fillMeasured 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 overA single statement takes most of the instance's activity.
Memory pressureQueries 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

Sources