SQL SERVER – Script to Find and Monitoring TempDB Space Usage

Monitoring tempdb space usage starts by separating data-file space from transaction-log space. In the client case behind this 2017 post, the tempdb data file was about 5 GB while its log file had grown to about 80 GB. Those sizes needed different checks.

The scripts below are read-only checks for SQL Server 2012 and later. The DMV queries require VIEW SERVER STATE on SQL Server 2019 and earlier, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Run them with an appropriately authorized diagnostic account.

Check Tempdb Data Files

Set the database context explicitly. The first result shows allocated file sizes, including the log. The second describes data-file allocations and free extents. Neither result tells you how much of the transaction log is active.

USE tempdb;
GO
SELECT name, type_desc,
       CAST(size AS bigint) / 128.0 AS file_size_MB
FROM sys.database_files
ORDER BY file_id;

SELECT SUM(CAST(unallocated_extent_page_count AS bigint)) / 128.0 AS free_extent_MB,
       SUM(CAST(user_object_reserved_page_count AS bigint)) / 128.0 AS user_object_MB,
       SUM(CAST(internal_object_reserved_page_count AS bigint)) / 128.0 AS internal_object_MB,
       SUM(CAST(version_store_reserved_page_count AS bigint)) / 128.0 AS version_store_MB
FROM sys.dm_db_file_space_usage;

SQL Server pages are 8 KB, so 128 pages make one MB in these reports. User objects include temporary tables and indexes. Internal objects include worktables, sorts and other engine workspace. The version-store column measures version-store reservations in tempdb; it is not a measure of every persistent version store in user databases.

SQL Server 2025 can also have a separate persistent version store inside tempdb when ADR is enabled there. Monitor that store separately; the traditional version-store counter alone is not a complete version-storage report for that configuration.

The free-extent counter is not every individually free page. Mixed extents and allocation bookkeeping mean these categories should not be forced into a perfect file-size reconciliation. Collect repeated samples to see whether usage is growing or simply reflects a large, mostly empty file.

Measure the Tempdb Log Directly

USE tempdb;
GO
SELECT total_log_size_in_bytes / 1048576.0 AS total_log_MB,
       used_log_space_in_bytes / 1048576.0 AS used_log_MB,
       used_log_space_in_percent,
       (total_log_size_in_bytes - used_log_space_in_bytes)
           / 1048576.0 AS free_log_MB
FROM sys.dm_db_log_space_usage;

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = DB_ID(N'tempdb');

A large log file can have little active usage. Conversely, a small file can be nearly full. log_reuse_wait_desc provides a starting point for investigating why space cannot be reused. For example, an active transaction can prevent truncation of log records that are still needed.

Log truncation makes internal log space reusable. It does not shrink the physical file on disk. Tempdb uses simple recovery, so taking a transaction-log backup is not the remedy for its log growth.

Find Current Task Allocations

USE tempdb;
GO
SELECT session_id, request_id,
       SUM(user_objects_alloc_page_count
           - user_objects_dealloc_page_count) / 128.0 AS user_net_MB,
       SUM(internal_objects_alloc_page_count
           - internal_objects_dealloc_page_count) / 128.0 AS internal_net_MB
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id
HAVING SUM(user_objects_alloc_page_count
           - user_objects_dealloc_page_count) <> 0
    OR SUM(internal_objects_alloc_page_count
           - internal_objects_dealloc_page_count) <> 0
ORDER BY internal_net_MB DESC, user_net_MB DESC;

These are net page-allocation counters grouped by session and request. They are useful for locating current work, but they are not transaction-log usage or a complete ownership ledger for every byte in tempdb. Task counters are aggregated into session counters as requests finish. Do not blindly add repeated session totals to every request row.

Find Transactions Using the Tempdb Log

SELECT t.transaction_id, st.session_id,
       t.database_transaction_begin_time,
       t.database_transaction_log_bytes_used / 1048576.0 AS log_used_MB,
       t.database_transaction_log_bytes_reserved / 1048576.0 AS log_reserved_MB,
       s.status AS session_status,
       r.status AS request_status, r.command, r.wait_type,
       r.blocking_session_id
FROM sys.dm_tran_database_transactions AS t
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = t.transaction_id
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = st.session_id
WHERE t.database_id = DB_ID(N'tempdb')
ORDER BY t.database_transaction_begin_time, st.session_id;

Compare the transaction start time, used bytes and reserved bytes with the session and request state. Reserved bytes are not the same as already-used bytes. A sleeping session can still own an open transaction, so the query uses left joins and keeps rows without an active request. The request columns show current activity on the same session; they do not prove that each displayed request owns that transaction.

A transaction can be associated with more than one session, and a session can have multiple requests. The result can therefore repeat transaction information. Use it to investigate individual transactions; do not sum the joined rows as a database-wide log total. The earlier log-space DMV provides that total.

Choose the Remedy After Finding the Cause

In the original case, the investigation found sessions running an external-directory query through OPENQUERY and loading a temporary table. That was a useful lead. The saved notes do not establish a general linked-server defect or prove that every similar query will cause the same problem.

Identify the owner and purpose of a long-running transaction before interrupting it. Fix the application or query and allow the transaction to complete when appropriate. If cancellation is necessary, plan for rollback time and its impact. Routine shrinking or restarting the instance hides evidence and can lead to repeated growth.

Keep enough preallocated space for the normal workload, use sensible growth settings and monitor the underlying disk. After an unusual event, any file-size adjustment should be a separate capacity decision. These scripts diagnose the situation; they do not issue KILL, shrink files or change server settings.

Monitoring tempdb is not just checking a file size, it is finding which kind of space the workload needs.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.