When tempdb grows unexpectedly in SQL Server, the root cause is often transaction or space allocation that is not properly released. Understanding how sessions use tempdb and how SQL tracks that usage helps you move from symptoms to a precise reason.
This article walks through a disciplined approach to find why tempdb is full, using practical queries, targeted checks, and operational context. You will learn how to connect the visible growth to its source workload.
| Session ID | Database | Active Command | Tempdb Usage Type | Space Used (MB) |
|---|---|---|---|---|
| 54 | tempdb | SELECT with ORDER BY | Internal Objects / Sort | 1250 |
| 73 | ReportingDB | INSERT EXEC procsp | Table-valued parameter | 890 |
| 112 | AppDB | Implicit transaction | Row versioning | 2100 |
| 140 | tempdb | Cursor fetch loop | Cursor retention | 650 |
| 203 | DB | Hash join | Hash memory grant spill | 3400 |
Identify Active Sessions Using Tempdb Space
Start by linking session activity to tempdb storage pressure. Use dynamic management views to find which sessions and commands are allocating pages in tempdb right now.
Look at allocations in two ways: internal objects for worktables and version store for row versioning. Each allocation type points to a different class of root cause.
Query Current Allocation by Session
Run the following to see session-level usage, command text, and the type of tempdb consumer.
SELECT s.session_id, s.host_name, s.program_name,
t.database_id, DB_NAME(t.database_id) AS tempdb_db,
t.user_objects_alloc_page_count * 8 AS user_obj_KB,
t.user_objects_dealloc_page_count * 8 AS user_obj_dealloc_KB,
t.internal_objects_alloc_page_count * 8 AS internal_obj_KB,
t.internal_objects_dealloc_page_count * 8 AS internal_obj_dealloc_KB,
t.version_store_alloc_page_count * 8 AS version_store_KB,
t.user_time, t.reads, t.writes, t.text
FROM sys.dm_db_session_space_usage AS t
JOIN sys.dm_exec_sessions AS s ON s.session_id = t.session_id
WHERE t.database_id = DB_ID('tempdb')
ORDER BY version_store_KB DESC, internal_obj_KB DESC;
Trace and Capture Ongoing Workload
Sometimes the active sessions at the moment of investigation are not the ones that caused the sustained growth. Capture workload over a window to find the pattern.
Combine execution traces with PerfMon counters so you can correlate I/O and memory pressure with specific queries.
Set Up a Lightweight Trace for Tempdb Consumers
Use server-side trace to capture attention for batch starting, attention, and transaction events on tempdb-related calls.
CREATE EVENT SESSION [Trace_Tempdb_Consumers] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text)
WHERE ([database_id]=(SELECT database_id FROM sys.databases WHERE name='tempdb'))
),
ADD EVENT sqlserver.transaction_log
(
ACTION(sqlserver.session_id, sqlserver.sql_text)
WHERE ([operation]=(16))
)
ADD TARGET package0.event_file(SET filename=N'TempdbConsumers.xel')
WITH (STARTUP_STATE=OFF);
Inspect Plan Caches and Recompilation Sources
Cached plans reveal objects and statements that repeatedly consume tempdb. Plans tied to spills, sorts, and hashes are priority suspects.
Correlate plan cache entries with execution metrics and tempdb allocation patterns to identify hot paths.
Find Plans with Spills or High Tempdb Allocations
Query plan cache metadata and runtime stats to expose queries that spill to disk or allocate large internal objects.
SELECT qs.execution_count,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_worker_time / qs.execution_count AS avg_cpu,
SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//RelOp[IndexSpool or Sort/RelOp[@PhysicalOp="Sort"]/Spill]') = 1
ORDER BY qs.total_logical_reads DESC;
Operational Patterns and Preventive Actions
Use findings from the queries, traces, and cache inspections to target specific fixes, whether it is query tuning, isolation level changes, or file sizing.
- Monitor version store size and versioned workload to set safe isolation levels and bound open transaction duration.
- Identify and rewrite queries with spills, sorts, and hash joins that push large datasets into tempdb.
- Right-size tempdb data files and enable multiple equally sized files to reduce PFS contention and allocation bottlenecks.
- Implement plan guides or optimize indexes to avoid repeated creation of internal objects and cached heavy plans.
- Automate proactive alerts on tempdb file growth, PFS contention, and transaction log flush waits to catch regressions early.
FAQ
Reader questions
How can I tell whether row versioning is driving tempdb growth?
Check the version store allocation counters in sys.dm_db_session_space_usage and correlate with transactions using snapshot isolation or change tracking.
What does a high user_objects_alloc_page_count indicate for tempdb?
It indicates that user-created objects such as table variables or table-valued parameters are consuming space, often caused by oversized batches or inefficient temporary object use.
Could blocking or long-running transactions be responsible for tempdb pressure?
Yes, when transactions remain open longer than necessary, especially those using snapshot isolation, the version store cannot clean up and continuously grows in tempdb.
How do I confirm that memory grant spills are the root cause of a full tempdb?
Look for spills in query plans, PerfMon memory grant waiting counters, and large hash or sort operators in the session’s active commands using the sys.dm_exec_requests and plan cache queries above.