2

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.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
Ben Amada
  • 283
  • 2
  • 9

1 Answers1

1

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?

While it's probably possible, I would say under normal circumstances the answer is no. In the absence of long-running transactions or long-lived sessions, the tempdb log should clear pretty quickly.

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)?

The most likely explanation is that there was a long-running transaction. It finally encountered this error and was killed, which allowed the log space to free up.

Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?

I would expect that to capture it. One thing that might have caused you to miss it is if the process had executed statements in a transaction and then just stopped doing anything (without committing, rolling back, or disconnecting).

This would be the case if someone ran a query in SSMS that used a ton of tempdb, and then just left that SSMS window open all day. These queries (that are holding resources, but not doing anything actively) won't show up in sp_WhoIsActive by default. You need to add the @show_sleeping_spids = 2 option to see those. Here's an excerpt from the built-in docs:

screenshot of show_sleeping_spids option description

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?

Not that I can think of. You might try issuing a CHECKPOINT, to force the VLFs to clear:

USE [tempdb];
GO
CHECKPOINT;
Josh Darnell
  • 29,228
  • 5
  • 65
  • 120
  • 1
    Thanks Josh for your input. Great tip on CHECKPOINT, I'll incorporate that into my log usage job and try running it if tempdb used space reaches 70%+ (this situation with the tempdb log growing to large percentages occurs about once a month). The @show_sleeping_spids = 2 parameter for sp_WhoIsActive also looks helpful. Since it outputs a lot of sleeping sessions with that option on, think I'll use it and delete any rows from the sp_WhoIsActive output which have open_tran_count, tempdb_allocations and tempdb_current all at 0. – Ben Amada Dec 18 '20 at 15:11
  • @[JoshDarnell] another one of these incidents occurred, fortunately this time I found out about it while it was ongoing thanks to an email alert I added. This time (possibly before too), it was a system SPID that was holding open a long running transaction. I discovered system processes can be captured in sp_WhoIsActive by adding @show_system_spids = 1. For reference, I posted a new question with a few details gathered (not many though). – Ben Amada Dec 19 '20 at 18:58
  • 1
    @BenAmada That's a really interesting follow-up question you put up. I've not come across that we're "sort_init" system spid activity, but I'll be interested to see if anyone else has ideas for you. – Josh Darnell Dec 21 '20 at 16:45