What causes this SqlException: a transport-level error occurred while receiving results from the server

Question

Here is the complete error: SqlException: A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation) SqlException: A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation)

I started looking at this message intermittently for several unit tests in my application (over 1100 units and system tests). I am using a test runner in ReSharper 4.1.

One more thing: my development machine is a VMWare virtual machine.

Answer 1

I came across this many years ago. On the bottom line, you have run out of available ports.

First, make sure your calling application has a connection pool.

If this happens, check the number of ports available for SQL Server.

What happens is that if the pool is disconnected, each call takes up a port, and by default it takes 4 minutes to expire the port and you have run out of ports.

If the pool is enabled, you need to profile all the SQL Server ports and make sure that you have enough and expand them if necessary.

When I encountered this error, the connection pool was disconnected, and this caused this problem when a decent load was added to the website. We did not see it in development, because the load was 2 or 3 people maximum, but as soon as the number grew by more than 10 people, we always saw this error. We turned on the pool, and he fixed it.

Answer 2

I came across this many years ago. However, in order not to dispute the explanation of @ Longhorn213s, but we had the exact opposite behavior. We got an error in development and testing, but not in production, where, obviously, the load was much greater. We ended up suffering a developmental problem, since it is sporadic and does not significantly slow down progress. I think that there may be several reasons for this error, but I could not indicate the reason myself.

Answer 3

We also encountered this error and found out that we were killing the SQL server connection to the database server. The client application is under the impression that the connection is still active and is trying to use this connection, but does not work because it was completed.

Answer 4

I saw this a lot when the network was scared or the NIC was on the way. Check the network stack.

Answer 5

We saw this in our midst and traced part of it to the “NOLOCK” prompt in our queries. We removed the NOLOCK hint and found that our servers use snapshot isolation mode, and the frequency of these errors has been reduced quite a bit.

Answer 6

We saw this error several times and tried different resolutions with varying degrees of success. One common main theme was that the system giving the error was low in memory. This is especially true if the server hosting the Sql server starts ANY other process than the OS. By default, SQL Server will grab any memory so that it can, and then leave little for other processes / drivers. This can lead to erratic behavior and intermittent messages. It is good practice to tune your SQL Server for maximum memory, which leaves some margin if there are other processes that might be needed. Example: Visual Studio on a dev machine that is running a copy of the SQL Server Developer Edition on the same machine.