Finding the root cause for the tempDB log file out of space error

Here is what to do when the tempDB log file runs out of space. You will not be able to run any sp_Who* queries. You will likely get this error shown below:

First, try running the DMV below to get the session data and see who the culprit SPID is:

…next see what open transactions are still running. The results will show you the SPID:

… next kill the SPID:

…now you can shrink the file (assuming it has been set to a max growth size and thus filled up:

Views – 14884