Thanks guys!!

The last time we faced this issue was on a Friday and tempdb grew during off hours i.e. when there was no one actively monitoring. When I was informed about this, I can tell there were many sessiosn running but I just couldn't isloate which one was culprit. Before I could do more research I was forced to restart SQL.

Index rebuilding sessions were surely not runnig as those are not done without DBAs knowing this. And we have a properly optimized tempdb with data and log files in different LUNs, proper auto-growth configuration, multiple data files etc. We have set the initial log file size to 40 GB but when it grows it suddenly goes up to 400 GB and thats what the limit of the drive is. Then all the processing stops with tempdb log file full errors and we are out of options.

And DBCC usertoptions shows isolation level as read committed.

I am just looking to capture the culprit (be it a long running session, or index rebuild or anything else). Is there a way to automate this so that whenever a growth on tempdb occurs, something gets logged somewhere (in a table, or errorlog etc.) I get to know that this particular thing is the problem.

Thanks again!!