When tempdb is filling, first determine whether the space belongs to user objects, internal work or row versions. Then identify the workload that creates or retains it. Adding files, restarting SQL Server or shrinking tempdb before making that distinction can remove evidence while leaving the original pressure unchanged.
The reference version is SQL Server 2022. This article explains source-checked, read-only observations and the decisions they support. It does not execute diagnostics, change file sizes or terminate transactions. SQL Server 2025 adds tempdb behaviour that must not be assumed on the 2022 reference system.
Three consumers, different questions
User objects include temporary tables and their indexes. Internal objects support engine work such as sorts, hashes and worktables. Version stores preserve row versions needed by features and transactions. Microsoft describes these as distinct uses of the shared tempdb resource.1, 2
The distinction matters operationally. A large temporary staging table suggests a different owner and lifetime from a query spilling internal work. Version-store retention can depend on an older transaction that still needs versions, rather than only the session currently generating changes.2
Do not equate every internal allocation with a memory spill. Internal work has several purposes. A spill explanation needs the relevant execution-plan or query evidence as well as the file-level allocation trend. Similarly, a high temporary-object count does not show which object accounts for most reserved pages.
Separate tempdb data-file pressure from its transaction-log pressure and from the underlying volume's capacity. This article starts with data-page allocation. If the error and file evidence point to the log, preserve that distinction and investigate its reuse and transaction state rather than applying a data-file remedy.
Confirm the failing capacity layer
Record the exact error, time, SQL instance, affected database operation and tempdb file paths. Compare allocated file size, free space within the files, permitted growth and free capacity on the backing volume. A file that has reached a configured maximum and a volume that cannot supply more space are different constraints.
Do not infer filesystem capacity from datastore free space alone. The guest volume, partition and virtual-disk mapping still matter. Likewise, adding another file to the same full volume cannot create capacity. Keep evidence about growth failures and other consumers of that volume with the SQL incident.
Use an existing DBA diagnostic role. For sys.dm_db_file_space_usage, Microsoft specifies VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, with VIEW SERVER STATE for the earlier SQL Server versions described in the reference.2
This author-composed example selects the documented counters. USE changes only the query connection's database context; it does not change database contents. The example is untested.
USE tempdb;
SELECT file_id,
unallocated_extent_page_count / 128.0 AS free_mib,
user_object_reserved_page_count / 128.0 AS user_object_mib,
internal_object_reserved_page_count / 128.0 AS internal_object_mib,
version_store_reserved_page_count / 128.0 AS version_store_mib
FROM sys.dm_db_file_space_usage;
SQL Server pages are 8 KiB; dividing a page count by 128 gives MiB. These are allocation counters, not exact logical row sizes. The documentation explains extent-level accounting and which allocation pages are excluded. Do not force the displayed categories to equal a simplistic total without considering those details.2
Capture the same fields at agreed intervals through bounded monitoring. A single large value shows current allocation, not whether it is growing rapidly or already being released. Keep the sample time and the monitoring reset/restart context with each result.
Attribute user and internal allocations
When user objects dominate, ask which application operations deliberately materialise data in tempdb. Examples to investigate include report staging and temporary intermediate results. The useful question is whether the object size and lifetime are appropriate for that operation, not whether temporary tables are inherently undesirable.
When internal objects dominate, identify the active requests and their plans. Compare estimated and actual work where suitable evidence exists, then investigate sorts, hashes or other internal operations. A memory-grant issue is one possible explanation, but it requires its own query-memory evidence.
Microsoft's tempdb guide provides session- and task-level views and example queries using sys.dm_db_session_space_usage and sys.dm_db_task_space_usage.1 Have the DBA use the version-matched examples to attribute allocation and deallocation. Do not treat a cumulative allocation counter as space still held: work can allocate and release repeatedly.
Task and session accounting also have different lifetimes. Preserve request and session identity, whether work is active or complete, and the time of observation. When correlating to an application request, avoid labelling a reused session number as the owner of an earlier allocation without additional evidence.
The next diagnostic step should be small enough to remain useful under pressure. Capturing every plan and every temporary object on a busy instance can create additional work. Select the most relevant candidate, retain its allocation trend and record why it was chosen.
Investigate version retention separately
Version-store pages are tracked at file level rather than assigned to tasks and sessions in the same way as user/internal allocations. Microsoft explains that a session's end does not necessarily allow all versions it generated to be removed; cleanup must consider the longest-running transaction that still needs the versions.2
Therefore, find both the producer of changes and transactions that may retain older versions. The referenced guide points to sys.dm_tran_active_snapshot_database_transactions and its elapsed-time information as part of that investigation.2 An old transaction is a candidate to assess, not authority to kill it.
Consider a hypothetical report that keeps a consistent view while another workload updates many rows. The updater may generate versions, while the report influences how long some versions remain necessary. Correcting only the update rate or blaming the most active writer can miss the retention condition.
Record accelerated database recovery settings and exact SQL version. The current documentation distinguishes traditional tempdb version storage from persistent version storage. SQL Server 2025 supports ADR in tempdb and can have two independent version stores there; that is not a SQL Server 2022 feature.1 Do not apply a 2025 configuration recommendation or assume a 2022 counter explains every store in a newer system.
Choose the change from the evidence
Use a decision table to separate immediate protection from the durable correction:
| Established condition | Decision to prepare |
|---|---|
| Legitimate workload exceeds planned reserve | Capacity and preallocation review, including growth limits and backing-volume reserve. |
| Temporary objects live longer than required | Application lifetime and cleanup correction, with failure-path testing. |
| Specific query generates excessive internal work | Query/plan investigation, including estimates and grants where relevant. |
| Long transaction retains needed row versions | Application/DBA decision about transaction completion and appropriate scope. |
| Allocation-latch contention with adequate space | Separate contention investigation; capacity exhaustion is not established. |
Microsoft recommends sizing tempdb for the workload and using consistent data-file sizing/growth arrangements.1 That guidance does not mean that increasing file count fixes every full-tempdb incident. File placement, growth and shared storage impact still require assessment.
Restarting the engine recreates tempdb, but also interrupts workloads and destroys the transient state being investigated.1 It is not a diagnostic first step. A controlled emergency restart, if ever approved by the service owner, needs its own impact and recovery plan and does not establish that the root cause has been fixed.
Do not delete temporary database files or arbitrary application objects. Do not reduce reserve through routine shrinking immediately after a peak. The required steady-state size should follow measured workload demand rather than an expectation that the files must always look small.
Verify without confusing release and shrink
Define the intended result before the change: the business job completes, tempdb retains adequate free allocation, the growth rate stays within the reserve and other workloads meet their limits. Free pages inside a file can become available for reuse without the physical file becoming smaller. That is a valid operational recovery when capacity and application acceptance are met.
Test application or query changes with representative synthetic data in an authorised environment, including concurrent workloads and cancellation. No such test was run here. A configuration change needs its previous state recorded, a scoped return plan and enough capacity for both the test and ordinary work.
After an approved correction, compare the same consumer categories and request evidence over a representative interval. Keep the original error and before/after data. A lower number after the workload stopped is not evidence that the next production run will fit.
Learning notes
- User objects, internal work and row versions require different attribution methods.
- Allocated pages are not the same as file bytes or logical data size.
- Version cleanup depends on transactions that still need older rows, and exact SQL version matters.
Acceptance checklist
- [ ] tempdb usage is attributed to user objects, internal work or versions with appropriate evidence.
- [ ] The relevant request or retained transaction is identified.
- [ ] Representative concurrent work completes within the agreed allocation and growth reserve.
- [ ] Reusable space is distinguished from physical shrink, and the return plan is preserved.
References
- tempdb Database - SQL Server | Microsoft Learn. Accessed 2026-11-01.
- sys.dm_db_file_space_usage (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.