Problem

Ran into an interesting one today. I’ve seen countless occurrences of tempdb filling up due to rogue queries that are poorly optimized. Today was the first time I’ve seen a full transaction log for tempdb result in me not being able to connect to the SQL instance at all.

Normal troubleshooting and intervention would look something like me getting an alert that tempdb is full in some aspect. In this case the alert that came in was like this:

The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’.

Then I would connect to the SQL instance and troubleshoot from there. Be it killing the query, or if the query already failed, then address the abnormal file growth of tempdb.

I tried connecting to the SQL instance in SSMS as I normally would and was met with the same error message as shown above. Made sure that my default database context was set to master and not tempdb, same issue and error. I even tried using the Dedicated Admin Connection (DAC) which is ADMIN:ServerName. No dice there because of some process using the DAC already and when I tried connecting, I got the “Only one DAC connection allowed” error message (I forget the exact error message off-hand but it’s something like that). If you’re lucky enough, the DAC should work in this case for you to log in, find the running query filling up tempdb and kill it, but I wasn’t so lucky.

Solution

I wasn’t left with many options so I restarted the SQL Server Service. This obviously was able to kill the offending query, and re-size tempdb to its normal values. Then retroactively, I found the query causing the issues. Turns out it was a process trying to create an index on a large temp table created and was running for over 3 hours. This seems to be an interesting edge case of how a full transaction log for tempdb resulted in me not even being able to connect to the SQL instance in SSMS.