Sporadic transport-level connection errors from SQL Azure

I have a page in web role that runs a stored procedure on SQL Azure that takes a fraction of a second to run the stored procedure and load the page. Sporadically, when that page is called, I get one of two errors:

1.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

2.
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An established connection was aborted by the software in your host machine.)

I’ve used “using” clauses for all my SqlClient connections and the stored procedure never takes longer than a fraction of a second to load. Here’s my code:

        Int16 retries = 0;


        TryAgain:

        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand();

                cmd.Connection = conn;
                cmd.CommandText = "SPROC Name";
                cmd.CommandType = CommandType.StoredProcedure;

  //Code for SPROC

                Int32 rowsAffected = cmd.ExecuteNonQuery();

                //Added conn.Close() to extraspecially make sure connection is closed!
                conn.Close();
            }
        }
        catch (Exception ex)
        {
            CommonFunctions.LogActivity("SQL Error (Retry): " + ex.Message);
            if (retries < 1)
            {
                retries++;
                //Added short sleep
                System.Threading.Thread.Sleep(50);
                goto TryAgain;
            }
            //Failed!
            CommonFunctions.LogActivity("SQL Error (Failed): " + ex.Message);
        }

I have a site monitoring company testing the url every 5-20 minutes that confirms this and reports the error anywhere between every 5 to 40 minutes.

I also created a windows azure app to monitor the web role twice a second to see the effect and the errors are still occuring with roughly the same frequency.

I have a workaround which is to sleep for 50 milliseconds and rerun the stored procedure again. The second attempt always works and prevents the error, but this is not ideal as speed is critical to the app and I'm concerned why this is happening.

Any ideas how I can prevent these errors?

Cheers

David

Can you provide your database and server  name plus the time when this happened recently?Thanks,Justyna

Thanks Justyna!Database Name: ChiLiteServer Name: jlb020vjemLog extract of last 20 errors  (DateTime is UTC):2009-12-02 23:09:00.727 SQL error  (retry): A transport-level  error has occurred  when receiving  results from the server. (provider: TCP Provider, error: 0 - An existing connection  was forcibly  closed by the remote  host.)2009-12-02 23:04:00.043 SQL Error (retry): A transport-level error has occurred when receiving results  from the server. (provider: TCP Provider, error: 0 - An existing  connection was forcibly closed  by the remote host.)2009-12-02 22:49:01.620 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 22:49:01.027 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 22:34:00.767 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 21:34:00.343 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 21:19:01.420 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 21:14:00.377 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 21:04:01.580 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 20:39:00.743 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 20:29:00.117 SQL Error (retry): A transport-level error has occurred when sending  the request  to the server. (provider: TCP Provider, error: 0 - An established  connection was aborted  by the software  in your host  machine.)2009-12-02 20:19:00.950 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 20:04:01.050 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 19:58:59.860 SQL Error (retry): A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)2009-12-02 19:09:00.517 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 18:44:00.830 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 18:19:00.293 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 17:49:00.647 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 17:39:00.750 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)2009-12-02 17:14:00.953 SQL Error (retry): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A few more questions: I assume this is one client application that gets all this errors  and one thread, is this true? Are you using client connection  pooling? (default is yes).During the time between the errors ex. 23:04:00 -  23:09:00.727 did you get any successful results? I can see about several active connecitons to our gateway during the time the error  ocurred and would like to know if this is something that your application was doing on purpose.That would help a lot!Thanks,Justyna