What’s the issue?
Tempdb has data files (typically configured as multiple files for contention reasons) and a single transaction log file. Under normal operation, the data files hold the bulk of tempdb’s storage usage, and the log file stays comparatively small because tempdb activity is short-lived and the log is truncated automatically as soon as transactions complete.This finding identifies instances where the tempdb log file has grown to a size larger than the combined size of the tempdb data files. The size relationship between data and log files is normally a good indicator of overall tempdb health, with an oversized log signaling transactional activity is occurring or has occurred at an unexpected pace.
Why is this a problem?
A log file larger than the data files can indicate that an open transaction has been active long enough or has performed enough work in tempdb to accumulate substantial log records that cannot be truncated until the transaction completes. The most common cause is a very large or long-running transaction that uses tempdb extensively, often through large temporary tables, table variables, or sort and hash operations.Sustained log growth in tempdb beyond the size of the data files can indicate an application or query pattern that needs review. Transactions should generally complete quickly, and one that produces enough log activity in tempdb to outgrow the data files often points to a query or batch that is doing more work in tempdb than necessary, or that is open longer than it should be.
This condition is also worth noting because tempdb log growth often competes for storage with the data files. If both are on the same volume (which is typical), an oversized log reduces space available for data file growth and can contribute to tempdb running out of space during heavier activity. The condition is rarely critical on its own but is a useful early indicator of issues in transaction or query design.
What should you do about this?
Identify the transactions and sessions currently active in tempdb for sessions consuming significant tempdb space, which you can do using sp_CheckTempdb with @Mode = 2. Capture the queries, the duration, and the user or application responsible for any long-running tempdb activity.Work with the application team to address the underlying cause when possible. Common improvements include shortening transaction durations, breaking large transactions into smaller units of work, reducing the size of temporary tables through better filtering, replacing large temporary table operations with set-based logic, and reviewing the isolation level choices that drive version store activity.
After the underlying activity completes and the log has been truncated, the log file itself remains at its grown size. Shrink the log to a more appropriate size with DBCC SHRINKFILE if the grown size exceeds normal operating needs, then size it appropriately for the actual workload going forward. Avoid repeated shrink and grow cycles, which produce VLF fragmentation and additional overhead.