A few days ago the amount of used space in both the tempdb data and log files started growing rapidly. After 30 minutes, the tempdb log remained around 99% space used for about 7 hours. Towards the end of the 7 hour period, I:\Databases\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered. was logged followed by about 30 instances of The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' over a 3 second period, and then suddenly the tempdb log file returned to 1% used space. This usage data is recorded in logs every 3 minutes via a SQL agent job.

I also have a SQL agent job that runs sp_WhoIsActive every 1 minute and captures the output. I don't see anything that stands out in this data that explains what was consuming or holding space in the tempdb. There are some maintenance jobs running for several minutes at a time, typically running no longer than 5 or 10 minutes each. Even if one of those jobs or another session used up tempdb log space, I would expect the space to be reclaimable after any transactions complete or at least after the session ends.

• It's a 200 GB drive dedicated for tempdb data and log files.
• Both the data and log files grew during this time until there was no more space on disk.
• There's a tempdb_2.ndf file I don't recall seeing before (maybe it's always been there...). tempdb.mdf and tempdb_2.ndf are the same exact size, around 29 GBs each. templog.ldf is using 137 GBs.
• Running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)

I'm trying to understand what happened and how to prevent it again.

• Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?
• What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)?
• Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?
• If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?

Any thoughts or suggestions are very appreciated.