SQL Server TempDB full: the shared scratch database that halts every query

Applications start failing with error 1105 (“could not allocate space for object in database ’tempdb’ because the ‘PRIMARY’ filegroup is full”) or error 3958, and the failures are not limited to one database. Every query on the instance that needs a temp table, a sort or hash spill, a worktable, or a row version touches TempDB. When TempDB cannot allocate space, all of them fail at once.

This is an instance-wide outage with a single shared point of failure, and it usually has one dominant consumer. The diagnostic job is short: identify which of the three consumer categories filled TempDB, find the sessions responsible, relieve the space pressure, then fix the root cause so it does not recur tomorrow.

Restarting SQL Server makes the symptom disappear because TempDB is recreated empty on every startup. It also destroys the evidence, interrupts every database on the instance, and the workload that filled TempDB the first time will usually fill it again. Treat restart as a last resort, not a fix.

What this means

TempDB is a system database shared by every database and every session on the instance. Space inside it splits into three consumer categories, and the split tells you where to look next:

• User objects: temp tables and table variables created by application code and sessions.
• Internal objects: sort and hash spills, worktables, spools. Large internal object consumption almost always means queries are spilling because their memory grants were underestimated (cardinality estimation errors, bad plans).
• Version store: row versions maintained for Read Committed Snapshot Isolation (RCSI), snapshot isolation, AlwaysOn readable secondaries, and online index operations. A growing version store almost always means one or more long-running transactions are pinning versions that cannot be cleaned up.

Two distinct failure modes share the same symptom of “TempDB problems” and should not be confused:

• Space exhaustion: TempDB files cannot grow (disk full, max size reached, autogrow disabled) and queries fail with error 1105 or 3958. This is what this article covers.
• Allocation contention: PAGELATCH waits on allocation pages (PFS, GAM, SGAM) in database ID 2 throttle throughput without any error message. That is a file-count and concurrency problem, not a space problem.

Common causes

Quick checks

All of these are read-only. The most important one first.

-- 1. Who owns the space? MUST run in the TempDB context;
-- sys.dm_db_file_space_usage is database-scoped.
USE tempdb;
SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- 2. Which sessions are consuming TempDB?
SELECT TOP 20
    session_id,
    (user_objects_alloc_page_count - user_objects_dealloc_page_count) * 8 / 1024 AS user_obj_mb,
    (internal_objects_alloc_page_count - internal_objects_dealloc_page_count) * 8 / 1024 AS internal_obj_mb
FROM sys.dm_db_session_space_usage
ORDER BY internal_obj_mb + user_obj_mb DESC;

-- 3. Is the TempDB volume itself out of room?
SELECT DISTINCT
    vs.volume_mount_point,
    vs.total_bytes / 1048576 AS total_mb,
    vs.available_bytes / 1048576 AS available_mb,
    CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,2)) AS pct_free
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE mf.database_id = 2;

-- 4. Have the TempDB files hit a configured ceiling?
-- max_size = -1 means unlimited; 0 with growth disabled means fixed.
SELECT name, size * 8 / 1024 AS current_mb,
       max_size, growth, is_percent_growth
FROM sys.database_files;
-- Run in TempDB context (USE tempdb) to see TempDB's own files.

-- 5. Confirm error 1105 / version store failures in the error log:
EXEC sp_readerrorlog 0, 1, 'Error: 1105';

-- 6. Is this actually allocation contention rather than space?
SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
  AND resource_description LIKE '2:%';  -- database_id 2 = tempdb

How to diagnose it

1. Run the space breakdown query (check 1) in the TempDB context. This is the fork in the road. The dominant column determines everything else. Forgetting USE tempdb is the most common mistake here; run against the wrong database and the numbers will mislead you.

2. Branch on the dominant consumer:

   • Version store is large: hunt long-running transactions. Find open transactions and their age, and identify which sessions hold them. On an AlwaysOn readable secondary, remember that readable secondaries generate version store rows locally, and cleanup on the secondary is gated by the oldest open transaction on the primary. The culprit may not be on the instance you are looking at.
   • Internal objects are large: queries are spilling. Correlate with memory grants: check sys.dm_exec_query_memory_grants for queries with large requested or granted memory, and check wait stats for RESOURCE_SEMAPHORE. Spills follow cardinality estimation errors and bad plans. If this started suddenly, look for a recent plan change or statistics update.
   • User objects are large: use sys.dm_db_session_space_usage (check 2) to rank sessions by net allocation, then inspect what those sessions are running. The usual suspects are reporting queries materializing huge temp tables, ETL staging, or application code building temp tables in loops.
   • No category dominates but free space is near zero: TempDB is simply undersized for the workload, the volume is out of room (check 3), or the files hit a max size ceiling (check 4).

3. Identify the responsible sessions and what they are executing before killing anything. Join the top consumers from sys.dm_db_session_space_usage to sys.dm_exec_requests and sys.dm_exec_sql_text to see the actual query text.

4. Decide: relieve, kill, or grow. If a single session or transaction is the cause and it is safe to terminate, killing it frees the space (after rollback completes). If the consumption is legitimate workload, the relief is more space: grow files, add a file on a volume with headroom, or free the volume.

flowchart TD
    A[Error 1105 / 3958
queries failing instance-wide] --> B[dm_db_file_space_usage
in tempdb context]
    B --> C{Which consumer
dominates?}
    C -->|Version store| D[Long-running transaction
RCSI / snapshot / AG secondary]
    C -->|Internal objects| E[Sort/hash spills
bad memory grants / bad plan]
    C -->|User objects| F[Temp tables / table variables
find session via dm_db_session_space_usage]
    C -->|None, free near zero| G[Undersized files / disk full
check volume + max_size]
    D --> H[Kill or wait out transaction
then grow headroom]
    E --> I[Fix plan / memory grant
then review spill volume]
    F --> J[Fix code or kill session
then right-size TempDB]
    G --> K[Grow files / add file
free volume space]

Metrics and signals to monitor

Fixes

Relieve space immediately

• Kill the offending session or transaction when one session is clearly responsible and the business cost of killing it is lower than the ongoing outage. Warn: rollback can take as long as the original work took, so space may not come back instantly. Confirm what the session is doing before you kill it.
• Grow the TempDB data files if the volume has headroom. Manual growth is preferable to relying on autogrow, which pauses I/O while the new space is initialized.
• Add a TempDB data file on a different volume with free space if the current volume is exhausted. This is a valid emergency relief move when you cannot free the existing volume quickly.
• Free the volume if other files (backups, exports) are sharing the TempDB drive. Backups written to the same volume as database files is a classic misconfiguration.

Fix the root cause by consumer type

• Version store: find and end the long-running transaction. On readable secondaries, look at the primary: the secondary’s version store cannot clean up versions still needed by the oldest transaction upstream. Long term, enforce transaction duration limits in application code and alert on old open transactions before they become TempDB incidents.
• Internal objects (spills): fix the plan. Update statistics, address the cardinality estimation error, force a known-good plan, or optimize the query. Right-sizing memory grants reduces spill volume directly. This class of problem recurs until the plan is fixed; adding disk only buys time.
• User objects: fix the code. Batch large temp table operations, replace repeated temp table creation with table variables or direct set operations where appropriate, and review reports and ETL that materialize large intermediate results.

Configuration fixes

• File count: Microsoft’s guidance is one TempDB data file per logical CPU up to 8, then add in multiples of 4 if allocation contention persists. This addresses PAGELATCH contention, not space, but undersized file count and space pressure often coexist on busy instances. Keep all data files the same size so round-robin allocation stays balanced.
• Pre-size files to the workload’s steady-state need. TempDB is recreated at its configured initial size on every restart, so an undersized initial size guarantees a burst of autogrow stalls after each restart.
• max size and autogrow: a max size ceiling or disabled autogrow turns “TempDB grows” into “error 1105” even when the disk has free space. If you cap growth, alert well below the cap. A ceiling without monitoring is a scheduled outage.

What about restarting?

A restart recreates TempDB empty and will clear the immediate failure. It also closes every connection, cold-starts the buffer pool for every database, discards the DMV evidence you need to find the root cause, and resets the clock on a workload that will likely refill it. Use it only when you cannot relieve space any other way, and capture the diagnostic queries above first.

Prevention

• Monitor TempDB space by category continuously, not just total free space. The three-way breakdown (user, internal, version store) is what turns a 3 a.m. page into a five-minute diagnosis.
• Alert on trend, not just threshold. Free space below 20% is investigation territory; below 10% is urgent. But version store growing steadily for hours is a warning long before either threshold trips.
• Alert on autogrow events. If TempDB grows during normal operations, it was not pre-sized, and every growth event stalled I/O while it happened.
• Alert on long-running transactions proportional to your TempDB headroom, especially on instances using RCSI, snapshot isolation, or readable secondaries.
• Pre-size and right-count the files: equal-sized files, one per logical CPU up to 8, sized for steady-state workload, on a volume with clear headroom and nothing else competing for it.
• Track memory grant health (grants pending, RESOURCE_SEMAPHORE waits) so spill-driven TempDB growth shows up as a memory problem before it shows up as a space problem.

How Netdata helps

• Netdata collects SQL Server performance counters and wait statistics per second, so TempDB autogrow stalls, PAGELATCH contention, and RESOURCE_SEMAPHORE waits are visible as they develop rather than after queries start failing.
• Tracking TempDB space by consumer category over time separates the slow version-store burn (hours, one bad transaction) from the fast spill-driven fill (minutes, one bad plan), which is the distinction that decides your first response.
• Correlating TempDB growth with memory grants pending and wait stats on the same timeline confirms or rules out spill-driven fills without querying DMVs by hand during the incident.
• Anomaly detection on version store size and free space catches the slow drift patterns that fixed thresholds miss until it is too late.
• Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server high compilations per second: plan cache pollution and CPU burn
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss