On one of our production Sql Server we ran into this interesting problem.

"A connection was successfully established with the server, but then an error occurred 
during the login process. (provider: TCP Provider, error: 0 - An existing connection 
was forcibly closed by the remote host.)"

I was attempting to login, using admin account, from SSMS from my laptop. So, I RDP’ed into the machine and fired up SSMS and tried to login using the same admin account. Same error again. Since login attempts from the same machine use ‘Shared Memory’, I thought I’d have better luck, but no success.

Upon further analysis, I see that memory usage is at 99.3%. That’s a big red flag. Surprise our monitoring system did not capture this memory uptick and did not raise any alert emails when it was consistently higher than 90%. Made a mental note to talk to the operations teams. Moving on..

I recollected that ‘max worker threads’ on this Sql Server was configured as ‘0’ (zero) i.e. unlimited threads a.k.a. unlimited connections. But in reality, unlimited in not possible. Sql Server continues to allow connections until it runs out of resources to allocate for new connections. This is one such situation. It is one thing to know this in theory and another to experience the issue. Never ran into this issue before, very interesting.

When a request comes to Sql Server it goes through several validation steps before it is granted connection into the instance. Once it successfully goes through all the tests, necessary resources are allocated and connection is established. Every connection gets a worker thread assigned to it to perform the task.

As part of it, Sql Server checks:

• If it there are any available threads. If yes, selects one of them for the new connection.
• If not, checks if the ‘max worker thread’ threshold is reached.
  • If not, increases the limit and created new threads.
  • If the limit is reached, no new connections are established. And you get this error. [In the error message it says “A connection was successfully established with the server, but then an error occurred“. It was able to establish connection, but ran into issues]

But in our case, the limit has not been reached. The value is set to unlimited (0 – zero). But when Sql Server tried to create new threads, it did not have any resources to assign to this new thread. Each thread needs about .5 MB of memory on a 32-bit machine and about 2 MB on a 64-bit machine. I guess Sql Server did not have any more free memory space to allocate to this new thread. Hence the error.

Side Note: Sql Seerver kills worker threads if they are idle for more than 15 mins.

So, I used DAC (Dedicated Administrator Connection) to login to Sql Server. Since it has its own dedicated resources (scheduler, memory node, etc), I was able to login and kill some blocking processes and idle SPIDS. Voila !! memory usage came back down and I was able to login using my other accounts.

Good Times !!

Best Practicies

• Each DBA that has access to production, please create dedicated accounts. Do not use ‘sa’ account for anyone. Where needed assign sysadmin permissions to dedicated accounts rather than using ‘sa’ account. It is easier to use ‘sa’ (that creating accounts for each person), but not good.

Hope this helps,
_Sqltimes