Database Connection Lost: Fix the 'Already Closed' Error in SQL Server

You're running a web app or a background service that talks to SQL Server, and suddenly you see this: The connection was closed by the server. It usually happens after the app has been idle for a while — maybe overnight, or after a lunch break. Or it pops up when your app tries to reuse a connection from a pool that's gone stale. You didn't change anything, but the database just won't talk to you anymore.

The root cause is pretty straightforward. SQL Server has an idle timeout — by default, it's 4 to 8 hours depending on your edition and configuration. When a connection sits untouched past that, the server unceremoniously kills it. But your connection pool on the app side doesn't know that. It still holds a reference to that dead connection. So when your code asks for a connection, the pool hands you a corpse. Your app tries to run a query on it, and boom — the server says "this connection is closed."

Another common trigger: a firewall or network device (like a load balancer) drops idle TCP connections after a shorter timeout — sometimes just 30 minutes. Your connection pool keeps the connection alive in its local cache, but the network path is gone. The first query after idle time hits that ghost connection and fails.

The fix isn't to restart SQL Server or your app. It's to handle this gracefully in your code and adjust a few settings. Here's how.

Fix 1: Add Connection Retry Logic — The Real Fix

This is the only fix that actually solves the problem long-term. Your code must expect connections to fail and retry them. Here's how to do it in C# with ADO.NET:

1. Open your data access layer — wherever you call SqlConnection.Open() or run a query.
2. Wrap the connection open and query in a retry loop. Use a simple for loop with a max of 3 attempts. Wait 1 second between retries.
3. On failure, explicitly close the broken connection with connection.Close() and dispose it. Then create a fresh SqlConnection object.
4. Catch SqlException with error number -2 (timeout) or 233 (closed connection). Don't retry on other errors like login failures — those won't fix themselves.

Here's a code snippet that shows the pattern:

int retries = 3;
while (retries > 0)
{
    try
    {
        using (var conn = new SqlConnection(connectionString))
        {
            conn.Open();
            // run your query
            break;
        }
    }
    catch (SqlException ex) when (ex.Number == -2 || ex.Number == 233)
    {
        retries--;
        if (retries == 0) throw;
        Thread.Sleep(1000);
    }
}

After implementing this, you should see the error disappear. The app will recover automatically after any transient network or server timeout.

Fix 2: Tweak Your Connection String

This isn't a replacement for retry logic, but it helps reduce how often the error occurs. Add these two settings to your connection string:

• Connection Lifetime — set this to 120 seconds. It tells the pool to recycle connections after 2 minutes, even if they're not idle. This prevents stale connections from lingering.
• Max Pool Size — set this to something reasonable like 100 or 200. Don't leave it at the default (100) if you have many concurrent users. Running out of pool slots also causes connection failures.

Your connection string should look something like this:

Server=myServer;Database=myDb;User Id=myUser;Password=myPass;
Connection Lifetime=120;Max Pool Size=100;

After you update the connection string in your app config or web.config, restart the application. You don't need to restart SQL Server.

Fix 3: Check Firewall and Network Timeouts

If the error happens exactly after 30 minutes or 60 minutes of idle time, your network gear is killing the connection. You've got two options:

1. Ask your network team to increase the idle timeout on the firewall or load balancer to at least 4 hours. This matches SQL Server's default.
2. Or, add a keep-alive query in your app. Run SELECT 1 every 15 minutes on a background thread to keep the connection alive. This is a band-aid but works when you can't change firewall rules.

To test if the firewall is the culprit, run this from your app server:

telnet yourSqlServer 1433

If it connects fine but drops after idle time, it's the firewall. If it never connects, you've got a port blocking issue.

What to Check If It Still Fails

Three things to look at next:

• SQL Server's remote query timeout. Run EXEC sp_configure 'remote query timeout' on the server. If it's set to 0 (infinite), change it to something like 600 seconds. Restart SQL Server after.
• Connection pooling is disabled. Check that Pooling=true is in your connection string. Some people set it to false thinking it helps — it doesn't. It just opens a new connection every time, which is slower and still can fail.
• Your app's thread pool is exhausted. If you see this error under high load, not idle time, you might be running out of worker threads. Increase Max Pool Size and check your app's thread count.

Bottom line: always code for failure. Networks drop. Servers reboot. Firewalls time out. A connection that worked 5 minutes ago can be dead now. The retry pattern handles all of that without you having to chase down every possible cause.