Context
SQL Server's tempdb is shared working space. Applications use it for temporary objects, while the engine uses it for operations such as sorts, hashes, and row-version storage. A full tempdb and an allocation-contention incident can look similar to users but require different corrections. Begin with the type of pressure and the workload creating it.
Prerequisites
Record SQL Server version, database compatibility settings, tempdb files, storage capacity, recent query changes, and row-versioning use. Obtain the documented diagnostic permissions for that release. Current Microsoft guidance includes SQL Server 2025 behavior: enabling accelerated database recovery in tempdb introduces a persistent version store there alongside its traditional version store. Review the actual feature state before comparing your system with an older monitoring script.
Identify the consumer
- Inspect allocated and free space in tempdb, then separate user-object, internal-object, and version-store usage through supported dynamic management views.
sys.dm_db_file_space_usage, queried in tempdb, is a read-only starting point. Record the sample time and file identity. A large preallocated file is not itself evidence that the same amount of space is currently occupied. - Correlate rising usage with sessions, tasks, execution plans, and scheduled jobs. Temporary staging tables point toward an application lifecycle, while large sort or hash work may point toward query shape or memory grants. Use scoped diagnostics instead of repeatedly scanning every plan on a busy instance. Protect query text that contains sensitive business values.
- Inspect waits and storage evidence separately from space consumption. Determine whether sessions wait on allocation structures, physical I/O, or some other resource. Adding files, adding capacity, and rewriting a spilling query address different problems. Review file sizing and growth consistency through the version-specific documentation rather than applying a fixed file-count rule without evidence.
- Check version-storage retention and transaction behavior when version space grows. Identify long-lived readers and the features generating versions. On SQL Server 2025 with tempdb ADR enabled, include both documented stores in capacity review. Do not assume every version-store metric describes the same data or that restarting the instance is an acceptable routine cleanup strategy.
Diagnostic table
| Evidence | Likely consumer or constraint | Next check |
|---|---|---|
| User-object usage rises with one job | Temporary staging lifecycle | Session and object cleanup |
| Internal usage rises during a report | Sort, hash, or intermediate work | Plan and memory-grant evidence |
| Version storage stays high | Retention by ongoing work | Transaction and feature state |
| Waits rise without space exhaustion | Allocation or I/O contention | Exact wait and storage timeline |
Illustrative scenario
A reporting release adds a broad sort over a larger result set. Internal-object usage grows rapidly, while user-object usage remains steady. The team tests a narrower query and appropriate access path on a representative copy, then compares tempdb use and request behavior. Adding temporary files would not by itself reduce the sort's required work.
Correct and verify
Apply the targeted query, transaction, storage, or file-layout correction through a tested change. Repeat the relevant workload and inspect both occupied space and waits. Confirm that cleanup occurs after the workload finishes and that headroom remains for overlapping jobs. Avoid shrink-and-grow cycles as a substitute for sizing the sustained working set.
Operating procedure
Monitor tempdb free space, growth events, consumer categories, and relevant waits together. Review capacity before large imports, index work, and reporting changes. Keep version and ADR feature state with dashboard definitions so upgrades do not silently invalidate the interpretation of version-storage metrics.
Explore Clouvenix server performance for implementation and operational handover.