What can cause a very large tempdb log file in SQL Server 2019?

Question

Yesterday, one of the SQL Servers of a customer crashed. The cause was a full hard disk. The full hard disk was caused by the tempdb-log file which was many hundreds of GB large. Luckily, it was a virtual server, so I could enlarge the disk, clean up the tempdb-log and everything is fine again. I cannot think of a reason, however, why this tempdb log file would have been so large. TempDB did have a simple recovery model, so the inactive part of the log should be cleaned every time the checkpoint command is run or an automatic checkpoint is executed. As far as I know, the automatic checkpoint will happen approx. every minute or so. The database did have three tempdb data files, each of about 64 MB. If a lot of data is inserted in a short time, the log can grow very large, but I would have expected that at least one of the data files would have also been very large. The error log did not contain anything that points to the cause of the very large tempdb log. Anybody any ideas?

Answer 1

Keep in mind that truncation of the transaction log is never done past the oldest transaction in the database. So if there was an open transaction in tempdb since two weeks the log will just keep growing.

The tempdb log was allowed to grow without bounds, which I think is a bad idea. What happened was that the log file took up all disk space, crashing the server (the os, not sql server). I would prefer the sql server stopping because the tempdb log is full, while the disk still has space left and the os is still running.

That's indeed the expected behaviour. I can't say why the server crashed, but if SQL Server asks the OS "Can I enlarge this file with 1GB?" and the OS responds "Sure, go ahead", but there is only 500 MB left, I guess the outcome can be bad. This sounds like an OS problem to. Or a problem with the VM platform, where the VM admins may have overallocated the disk spaces for the VMs.

Answer 2

Both of you, thank you for your help. I found the culprit: A function that when called with a NULL parameter came into an endless loop causing ever more temp data to be generated. Calling the function with NULL input doesn't make sense, so a simple WITH RETURNS NULL ON NULL INPUT solved the problem.