A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
If you are reading this, you may have searched for this error/exception:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
This error will show if a connection is drawn from the connection pool and the connection to the server has been lost.
There is no way for a connection in the pool to know that the connection has been severed.
From “SQL Server Connection Pooling (ADO.NET)”
https://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
Removing Connections
The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed.
Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid.
Invalid connections are removed from the connection pool only when they are closed or reclaimed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid.
This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur.
When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.
Basically what you are seeing is that exception in the last sentence.
A connection is taken from the connection pool, the application does not know that the physical connection is gone, an attempt to use it is done under the assumption that the physical connection is still there.
And you get your exception.
There are a few common reasons for this.
.1 The server has been restarted, this will close the existing connections.
In this case, have a look at the SQL Server log, usually found at: C:\Program Files\Microsoft SQL Server\<your instance>\MSSQL\LOG
If the timestamp for startup is very recent, then we can suspect that this is what caused the error. Try to correlate this timestamp with the time of exception.
2009-04-16 11:32:15.62 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2. Someone or something has killed the SPID that is being used.
Again, take a look in the SQL Server log. If you find a kill, try to correlate this timestamp with the time of exception.
2009-04-16 11:34:09.57 spidXX Process ID XX was killed by hostname xxxxx, host process ID XXXX.
3. There is a failover (in a mirror setup for example) again, take a look in the SQL Server log.
If there is a failover, try to correlate this timestamp with the time of exception.
2009-04-16 11:35:12.93 spidXX The mirrored database "<your database>" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
There may be more server actions that causes this; I will add them as I find them.
This may also be caused by other, non SQL Server related, reasons.
I have for example seen issues where the TCP Chimney Offload feature on the server machine is turned ON, causing this. Short info about this feature;
When TCP Chimney Offload is enabled and the NIC implements what is called the TCP Offload Engine, then some of the TCP processing is handed over to the hardware, i.e. the NIC.
By doing this, the CPU is offloaded, and since TCP could require a lot of processing this would mean that the CPU will be allowed to perform other tasks.
More information here: https://technet.microsoft.com/en-us/library/bb878074.aspx
I’ve also seen issues where the customer is using a content switch (usually for load balancing) this sometimes uses what is called ‘stickiness’ in order to make sure that a client always
connects to the same server. Some of these switches have a timeout for inactivity. Once this timeout is hit, the connection closed and removed, causing the exception above.
Note that this error is not limited to .Net applications, the same thing will happen if you are connected using cached or active connections (SSMS or SQLCMD for example).
SSMS:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
SQLCMD:
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
So, let’s finish with an example (since I like those) using the KILL example above (less intrusive than restarting the server).
Create a new .Net C# console application; in the Main method enter this:
string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=<your database>";
try
{
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText = "SELECT 1 AS Test";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@SPID";
Int16 spid = (Int16)cmd.ExecuteScalar();
Console.WriteLine("Take note of the SPID -> {0}", spid);
Console.WriteLine("Then kill that spid on SQL Server, and hit enter");
Console.ReadLine();
cmd.CommandText = "SELECT 1 AS Test";
cmd.ExecuteNonQuery();
}
catch (SqlException se)
{
Console.WriteLine(se);
}
And run it, when prompted, kill the SPID from SSMS, for example by running the KILL command.
In my case the output will be:
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Hope this helps.
Comments
Anonymous
April 16, 2009
PingBack from http://asp-net-hosting.simplynetdev.com/a-transport-level-error-has-occurred-when-sending-the-request-to-the-server-provider-tcp-provider-error-0-an-existing-connection-was-forcibly-closed-by-the-remote-host/Anonymous
December 16, 2010
TCP Chimney was the offending issue for us . We turned this off and the error disappeared . I followed the following articles to do this . www.alacritech.com/Support/FAQs/DisableOffload.aspx blogs.msdn.com/b/psssql/archive/2010/02/21/tcp-offloading-again.aspx support.microsoft.com/.../951037 This article helped locate the problem, Thanks! AAnonymous
January 18, 2011
You mention that this could be caused by the TCP Chimney Offload feature and then describe the chimney offloading, which sounds only positive - why would this technology cause issues if it's on, since its purpose is to free up the CPU? What are the downsides, and when should it be disabled or left enabled? Is there a way to definitively prove that the chimney setting is causing this issue?Anonymous
August 25, 2011
None of this helped us. We are trying the Chimney: disable again, but it didn't work before. We are on SQL 2005 + Windows 2008 Server. I've read that this has something to do with SQL 2005 and that upgrading to SQL 2008 would help. What is the confidence level on this fix? Any other suggestions? This is a .NET 3.5 OLEDB Windows application.Anonymous
November 09, 2011
This issue has been fixed in .NET 4.0 Reliability Update. Please see this posting from the team's website blogs.msdn.com/.../minimizing-connection-pool-errors-in-sql-azure.aspx Kathy Lu ADO.NET Team, MicrosoftAnonymous
April 30, 2013
Got this error during a firewall fail over. Server appeared healthy after reconnecting but the .NET guys continued to have trouble with their applications for a few minutes afterwards.Anonymous
August 19, 2013
I was using Azure SQL server 2012. I faced the same issue, but what really happened in my case is one of the fields in the table I am inserting into has a character limit varchar(50), some of my rows crossed that limit and I got "A transport-level error has occurred when receiving results from the server. (provider: Session Provider,error: 19 -Physical connection is not usable)". This may be because the connection was closed by server because of the exception. This error description is the least helpful one. What I suggest is to in a loop add single row to the table and see if it goes through. If it failed on a specific row, compare the row with other rows and check the data of the row for any anomalies. My code was. using (SqlConnection con = new SqlConnection(constring)) { con.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con)) { bulkCopy.DestinationTableName = "dbo.Table"; bulkCopy.BatchSize = 50; try { bulkCopy.WriteToServer(clips); } catch (Exception ex) { Console.WriteLine(ex.Message); } } }Anonymous
November 13, 2013
I did a 24-hour test (repeat the same query in 24 hours) using 2 Clients and 2 SQL Servers. Only Client 1 has this problem with SQL Server 1 (around 0.3% query failed because of this error). Client 1 has no problem with Server 2, and Client 2 has not problem with Server 1 and Server 2. How do you explain it? If you say Server 1 has problem, then why Client 2 never fails with Server 1? If you say Client 1 has problem, then why Client 1 never fails with Server 2? Thanks.Anonymous
November 17, 2013
string cs = @"Data Source=<your server>;Integrated Security=SSPI;Initial Catalog=<your database>"; try { SqlConnection con = new SqlConnection(cs); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; con.Open(); cmd.CommandText = "SELECT 1 AS Test"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT @@SPID"; Int16 spid = (Int16)cmd.ExecuteScalar(); Console.WriteLine("Take note of the SPID -> {0}", spid); Console.WriteLine("Then kill that spid on SQL Server, and hit enter"); Console.ReadLine(); cmd.CommandText = "SELECT 1 AS Test"; cmd.ExecuteNonQuery(); } catch (SqlException se) { Console.WriteLine(se); }Anonymous
March 23, 2014
Use clearpool(urSQLconnection), along with dispose. This will clear the dirty connection. and you can open new connection.