SQL TCP/IP Connections Dropping

A while ago we were plagued at a client by connections that were being forcibly closed by SQL on our Live Production DB. At first I presumed this a network error at the client because in the event log on the server, the error, "Windows Domain Controller Could Not be Found" occurred often, so at first I thought that the network connection was then being dropped by the server losing connection to the network for some or other reason, this meant I escalated the issue to networks and the end result is we sat one night and redid the box, but the error came back and caused the same problems as before.

So after more investigation, I found that there were cases on the net where people had the same isses as us...

• TCP/IP Connection was forcibly closed by the software in your machine
• Operation cannot be completed because the Socket is not a Socket.
• Software in your Remote Host has forcibly closed your connection.

The fix I applied which has led to 3 weeks of no further issues being logged is as follows...

• Open SQL Server Configuration Manager on your DB Server
• Expand the node SQL Native Client Configuration and click on Protocols
• Ensure that the Shared Memory option is disabled and that TCP/IP is ordered 1 and Named Pipes is 2

This fixed the problems we were having, again I only suggest this fix if you encounter the problems described above, obviously trying to understand what this fix does, which the guys on net coudn't explain was that Shared Memory was enabled and ordered 1, thus when the server ever took alot of strain, or had many connections, even though the connection limit was set to unlimited, it seems SQL would then go and close random/dormant/sleeping TCP Connections cause it gave priority to Shared Memory even though we never used Shared Memory.

Take note, though, becareful when disabling Shared Memory if you have applications that run on the SQL Box, so for example if we have web apps and the DB on the same server, this could end up breaking your application.

- sent by Robert aka Robbie aka Rabobi