Huge tempDB Log File
Published: 23rd September 2013
I had an interesting problem appear this morning in which the tempDB Log file began to grow… and by grow I mean it went from just 10GB to well over 150GB in one morning and showed no signs of stopping.

I’m used to the data file growing as required, but it’s incredibly rare I see such behaviour from the log.

Obviously this also was less than ideal as the small SSD which houses our tempDB began to rapidly run out of free space.

A quick bit of playing with “dbcc opentran” (which I’ll show in a minute) led me to the cause and, as it turns out, this was being caused by some code which, although it looked sensible, was far from wise given the circumstances…

So… now for the fun part.

This is what I was faced with:

I had a quick look through sysprocesses looking for log running transactions hitting tempDB but there was nothing. Very strange.

There were a few long running spids, but that wasn’t unusual for the server in question, so I was at a loss as to how to narrow down the troublemaker.

Then it hit me. I ran dbcc opentran (note that this has to be run in the database you wish to query, therefore in this case tempDB) and saw the following:

Note that I was running this at 9am and therefore you can see there was a transaction which had been open for 3 hours and therefore meant that the tempDB log could not cycle and kept growing.

Following this, I simply looked at the spid, found the job that was running, and investigated.

As it turns out, what was happening was that someone was calling 10 stored procedures in turn, but instead of hard coding them, they had created a temporary table, populated it with the stored procedure names, and were running them one after another.

Therefore as it turned out, the temporary table was created and then held until the last of the 10 procs executed. This was what was causing all the problems as it held the one transaction open in tempDB for a the entire job run. A simple switch to make the temporary table into a real table and the issue immediately went away.
NB: Comments will only appear once they have been moderated.