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)”

http://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: http://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.