Troubleshooting TempDB Log Full Errors When SSMS Won’t Connect

Have you ever received the dreaded error from SQL Server that the TempDB log file is full? Only to open SSMS and be greeted with a message that prevents you from connecting.

Oh no.. my number one troubleshooting tool is not usable. Time to fire up a command prompt and connect via DAC, right?

Well, not so fast.

During a recent technical interview, I was introduced to a clever workaround that lets you connect to a distressed SQL Server using SSMS, even when it seems unresponsive.

The SSMS Workaround

Here’s how to bypass the connection issue and get back into SSMS:

1. Open SSMS and connect to a healthy SQL Server instance (not the one experiencing issues).
2. Open a New Query window.
3. In the query window, click the Change Connection icon (top-left corner).
4. Enter the connection details for the problematic server.

Volla, you now have a query window connected to the troubled instance. From here, you can begin your investigation.

Diagnosing the Issue

Start by identifying active sessions. For example:

SELECT session_id, blocking_session_id, wait_type, wait_time, status
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('tempdb');

Let’s say you discover that SPID 57 is causing blocking in TempDB. You can dig deeper with:

DBCC INPUTBUFFER(57);

Once you’ve confirmed the culprit, you can terminate the session:

KILL 57;

After that, try reconnecting to SSMS normally. With the blocking session cleared, you should be able to perform a deeper dive into TempDB usage or other root causes.

Bonus: Using the DAC (Dedicated Administrator Connection)

If SSMS still refuses to cooperate, you can fall back on the DAC.

1. Open a Command Prompt or PowerShell.
2. Connect using:

sqlcmd -S admin:localhost2017 -E

This gives you a direct admin connection to the server, bypassing many of the usual constraints.

The post Troubleshooting TempDB Log Full Errors When SSMS Won’t Connect appeared first on GarryBargsley.com.