What causes this SqlException: A transport-level error has occurred when receiving results from the server

Question

Here is the full 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)

I've started seeing this message intermittently for a few of the unit tests in my application (there are over 1100 unit & system tests). I'm using the test runner in ReSharper 4.1.

One other thing, my development machine is a VMWare virtual machine.

Ebracteate

Answer 1

I ran into this many moons ago. Bottom line is you are running out of available ports.

First make sure your calling application has connection pooling on.

If that does then check the number of available ports for the SQL Server.

What is happening is that if pooling is off then every call takes a port and it takes by default 4 minutes to have the port expire, and you are running out of ports.

If pooling is on then you need to profile all the ports of SQL Server and make sure you have enough and expand them if necessary.

When I came across this error, connection pooling was off and it caused this issue whenever a decent load was put on the website. We did not see it in development because the load was 2 or 3 people at max, but once the number grew over 10 we kept seeing this error. We turned pooling on, and it fixed it.

Rochdale

Answer 2

I ran into this many moons ago as well. However, not to discount @Longhorn213s explanation, but we had the exact opposite behavior. We received the error in development and testing, but not production where obviously the load was much greater. We ended up tolerating the issue in development as it was sporadic and didn't materially slow down progress. I think there could be several reasons for this error, but was never able to pin point the cause myself.

Eurythermal

As Trumpi suggests, I've found this error can occur simply by cycling the DB server (or anything that forces the server to release the connection). The connection is still cached at some level from the other end (in the client software, or in a pool) and then during the attempt to use it, the discovery that it is no longer valid returns "transport-level error". So it's probably because you don't reboot your production DB server (a good thing) and you do reboot your dev DB server (who cares?). – Nicko

Answer 3

We've also run across this error and figured out that we were killing a SQL server connection from the database server. The client application is under the impression that the connection is still active and tries make use of that connection, but fails because it was terminated.

Whisky

Answer 4

We saw this in our environment, and traced part of it down to the "NOLOCK" hint in our queries. We removed the NOLOCK hint and set our servers to use Snapshot Isolation mode, and the frequency of these errors was reduced quite a bit.

Stallfeed

Answer 5

We have seen this error a few times and tried different resolutions with varying success. One common underlying theme has been that the system giving the error was running low on memory. This is especially true if the server that is hosting Sql Server is running ANY other non-OS process. By default SQL Server will grab any memory that it can, then if leaving little for other processes/drivers. This can cause erratic behavior and intermittent messages. It is good practice to configure your SQL Server for a maximum memory that leaves some headroom is there are other processes that might need it. Example: Visual Studio on a dev machine that is running a copy of SQL Server developers edition on the same machine.

Cathrinecathryn

Answer 6

See the SQL Server logs. This error may occur when you have a BLOB column in the table and are reading data (with nolock).

I found in my SQL Server log the following message:

A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

This caused an error:

A transport-level error has occurred when receiving results from the server.

Aishaaisle