Thanks for the question and the comment thread.
Does anyone know why it is so difficult (it seems) to know exactly which queries or SPIDs are the culprits for these cases of huge increases in tempdb log space being used?
I know there are many variations of checking for likely causes, such as the types of diagnostics suggested in the StackExchange page here:
you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query using DBCC INPUTBUFFER but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:
Most of the caveats that Aaron Bertrand provides in his comments seem to have to do with trying to capture details for a query that may no longer be running. But is there any way to capture such a query as its tempdb log space usage crosses certain thresholds (such as 50% of the available tempdb log space) - so at least that info could be logged somewhere before the query stops, log fills up, or the SQL service needs to be restarted (heaven forbid)?
The reason I am very interested in this is that in the recent past I faced a similar issue with tempdb log space getting used up - apparently by a runaway query - but I was never able to find the root cause. So I am constantly apprehensive that it could happen again because I don't know what caused it.
Thanks for any help.