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
tempdbdata 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.ndffile I don't recall seeing before (maybe it's always been there...).
tempdb_2.ndfare the same exact size, around 29 GBs each.
templog.ldfis 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
tempdblog space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?
- What caused
tempdblog space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and
tempdbtransaction log is full)?
sp_WhoIsActivelogging not enough to capture what process was holding onto the
tempdblog space, what other additional logging can I do?
- If the
tempdblog 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.