Troubleshooting TempDB Log Full Errors When SSMS Won’t Connect – SQLServerCentral

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.