SQL Azure Connection Retry Update

Based on several e-mail conversations, I’ve updated the retry code slightly and made a couple of improvements.  The code in the original post is updated and it’s also included in this post for RSS purposes.  We haven’t deployed this version to production yet, so I don’t have any real-world results to report.

The first change I made was to do my math correctly in the sleep computation …

The second change I made was to add a SqlConnection.ClearPool(conn); call to the retry exception handler.  This should ensure that the other connections in the pool don’t have to retry.  We tend to see our retries in “batches”, so hopefully this is the line of code to fix that.  You could use ClearAllPools() instead, but since we have a database per tenant, clearing the specific pool is a better choice for us.

The third change was to move the conn.Close() and SqlConnection.ClearPool(conn) outside of the if block so that they get executed every time.

The fourth change was to add one final Sleep(sqlRetrySleep) call in the exception handler.  This and the ClearPool call gives us our best chance to succeed when we return.

The last thing I did was to use Actipro CodeHighlighter to make the code in the blog post more readable.

Thanks to everyone for the feedback.  Here’s the latest version of the code: 

public string GetSqlContextInfo(SqlConnection conn) { string sqlContext = string.Empty; // Omitted the code to read these from configuration int sqlMaxRetries = 4; int sqlRetrySleep = 100; int sqlMaxSleep = 5000; int sqlMinSleep = 10; // start a timer TimeSpan ts; DateTime dt = DateTime.UtcNow; for (int retryCount = 1; retryCount <= sqlMaxRetries; retryCount++) { try { conn.Open(); // get the SQL Context and validate the connection is still valid using (SqlCommand cmd = new SqlCommand("SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())", conn)) { sqlContext = cmd.ExecuteScalar().ToString(); } ts = DateTime.UtcNow - dt; // log opens that take too long if (ts.TotalMilliseconds >= 75) { SxpLog.WriteKnownEvent(8001, ts.TotalMilliseconds, "Connect", conn.DataSource, conn.Database, conn.WorkstationId, sqlContext); } break; } catch (SqlException ex) { conn.Close();
if (retryCount == 1)
{ SqlConnection.ClearPool(conn);
} if (retryCount < sqlMaxRetries) { SxpLog.WriteSqlRetry(5902, ex, conn.DataSource, conn.Database, conn.WorkstationId, "Connect", retryCount); // don't sleep on the first retry // Most SQL Azure retries work on the first retry with no sleep if (retryCount > 1) { // wait longer between each retry int sleep = retryCount * retryCount * sqlRetrySleep; // limit to the min and max retry values if (sleep > sqlMaxSleep) { sleep = sqlMaxSleep; } else if (sleep < sqlMinSleep) { sleep = sqlMinSleep; } // sleep System.Threading.Thread.Sleep(sleep); } } else { // Log the exception SxpLog.WriteSqlException(ex, conn.DataSource, conn.Database, conn.WorkstationId, "Connect", retryCount, "Final"); // we thought about rethrowing the exception, but chose not to // this will give us one more chance to execute the request - we might get lucky ...
// sleep System.Threading.Thread.Sleep(sqlRetrySleep);
conn.Open(); } } } // we log this value and null might cause an issue if (sqlContext == null) { sqlContext = string.Empty; } return sqlContext; }
Comments (0)

Skip to main content