Solution for: A transport-level error has occurred when receiving results from the server…

We have a program written in C# using Visual Studio 2012 and .NET 4.0 / .NET 4.5. It rapidly executes many queries in Microsoft SQL Server 2008 R2 and then exits. Executed query count varies from several hundred to many thousand.

Today, when executed particular task with very many queries, I noticed an error in the log file:
A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at MyTestApp.Program.QueryOneLineOneValue(String cmdText)
at MyTestApp.Program.Worker(String[] args)
at MyTestApp.Program.Main(String[] args)


At first, the error message lead me to think that something is wrong with the network. “Physical connection is not usable”… anyway it didn’t say anything useful… and SQL Server log file didn’t have anything useful either.

A quick Google search revealed that many people have the same problem:

1. Minimizing Connection Pool errors in SQL Azure — it has code example with an serious bug, and it leads to Reliability Update 1 for the .NET Framework 4 which may help judging from the description – “Issue 14 – A transport-level error has occurred when sending the request to the server.” I didn’t tried it, mostly because it talks about Azure services.
2. Discussion in the Microsoft SQL Server Database Engine forum which leads to MSDN article about SQL Server Connection Pooling which again does not help much, because I have only one connection in my application.
3. And a couple not very useful pages from the Stack Overflow: One suggests to call ClearAllPools and another one is about Azure again.

Again, I had to find solution by myself. I looked at the code and didn’t see anything unusual. I did another run under the VS debugger, and after a while the same exception was thrown. Looking under debug log file, I noticed that program stopped in the same place that in the production server. And by the same place I mean, the same amount of SQL commands were executed.

I quickly put a counter on right before SQL command execution and found that .NET throws exception always on 32767 command. 32767 is very familiar constant for programmers, and it is obvious that some resource leakage is happening. A quick glance at the code again revealed that a call to Close or Using statement is missing.

SqlConnection conn;
   
private static void QueryOneLineOneValue(String cmdText)
{
      string q = "SELECT ... FROM cmdText...";
      SqlCommand command =
         new SqlCommand(q, conn);
   
      SqlDataReader reader = command.ExecuteReader();
   
      while (reader.Read())
      {
         //do something with the data...
      }
   
      reader.Close(); //this line was missing
}