Fix SQL Server Connection Drops After Idle Period

You're working on a report. You step away for coffee. When you come back and run your query, SQL Server says "Connection lost" or "TCP provider: Error code 0x68". This happens after exactly 5–10 minutes of no activity. I've seen this happen with SQL Server 2019, 2022, and even Azure SQL Managed Instances when you're using a VPN or a load balancer.

So what's really going on?

Your database connection didn't just get bored and leave. The network equipment (firewalls, load balancers, or even your router) is programmed to drop connections that don't send any data for a certain amount of time. The default is usually 5 to 10 minutes. SQL Server doesn't send keepalive packets by default on every connection. So when the firewall kills the connection, your app or SSMS still thinks it's alive. Then you try to run a query and it fails.

There are two main causes:

TCP keepalive is off or set too high. This is the network-level heartbeat that tells the firewall "I'm still here."
Connection pooling on the app side caches dead connections. Your application picks a dead connection from the pool and tries to use it.

The real fix is to set the keepalive interval low enough and also configure the connection pool to validate connections before using them.

Step-by-step fix for SQL Server connection drops after idle

Set TCP keepalive on the SQL Server.
This tells the server to send a tiny packet every few seconds to keep the connection alive. Open SQL Server Configuration Manager. Go to SQL Server Network Configuration -> Protocols for [Your Instance] -> right-click TCP/IP -> Properties. Click the IP Addresses tab. Scroll down to IPAll. Set the following two values:
```
KeepAliveInterval = 1000 (milliseconds – that's 1 second)
KeepAliveTime = 30000 (milliseconds – that's 30 seconds)
```
After you set these, click OK. Then restart the SQL Server service. Right-click the SQL Server service in Configuration Manager and choose Restart. After restart, the server will send a keepalive packet every 30 seconds. The firewall sees that and won't drop the connection.
Tell your application to validate connections before using them.
This part depends on what you're using. In .NET (C# or VB.NET), add this to your connection string:
```
"Server=myServer;Database=myDB;Integrated Security=True;Connection Lifetime=0;Connection Reset=True;
    Pooling=True;Min Pool Size=0;Max Pool Size=100;"
```
The key is Connection Lifetime=0. That means the connection pool will automatically remove any connection that's been idle for too long. If you're using a different language like Python or Java, check the connection string docs for tcp_keepalive or connection_timeout settings.
Check the firewall and load balancer settings.
Your network team might have set an idle timeout on the firewall that's way too low. Ask them to set the TCP idle timeout to at least 30 minutes. If they won't do that, you can set the keepalive on the SQL Server side like in step 1. Also check the load balancer if you're using one – they often have a default idle timeout of 5 minutes.
Test the fix.
Open SSMS and connect to your SQL Server. Run a simple query like SELECT GETDATE(). Then close SSMS but don't disconnect – just leave it open. Go for a 15-minute walk. Come back and run the same query. If it works without error, you fixed it. If it still fails, go to the next section.

What to check if it still fails

Check the SQL Server error log. Run this command: EXEC xp_readerrorlog 0, 1, 'idle'. Look for any lines that say "A connection was dropped because of idle timeout" or similar. If you see them, the keepalive might not be set correctly.
Verify keepalive settings are actually applied. Run this query: SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID. Look for the net_transport column – if it says "Session", you're on a local pipe (not TCP). That means you're using named pipes, not TCP/IP. The keepalive setting doesn't apply to named pipes. Switch to TCP/IP in your connection string.
Try a different client. If SSMS fails but a different app (like a Python script) works, the problem might be in SSMS's connection settings. In SSMS, go to Tools -> Options -> Query Execution -> SQL Server -> Advanced. Set Connection idle timeout to 0 (never). Apply and restart SSMS.
Last resort – disable connection pooling temporarily. In your connection string, add Pooling=False. This forces a new connection for every query. It's slower, but it bypasses the idle-drop issue completely. Use this only for testing. For production, you want pooling but with the keepalive fix.

I've fixed this exact problem dozens of times. 90% of the time, setting those two keepalive values on the server side does the trick. The other 10% is a misbehaving firewall or a load balancer with a stupidly low timeout. You got this.