Sql Server Transient Exception Numbers
<p>I want to write some wrapper code to my database calls (using C#, and a Microsoft technology for accessing the database), auto-retrying on a 'transient' exception. By transient, I mean something that there is a good chance will resolve eventually (as against logically errors that will never work). Examples that I can think of include:</p>

<ul>
<li>Deadlock</li>
<li>Connection Timeout</li>
<li>Command timeout</li>
</ul>

<p>I had planned on using SqlException's error numbers to spot these. So for example:</p>

<pre><code>List&lt;RunStoredProcedureResultType&gt; resultSet = null;
int limit = 3;
for (int i = 0; i &lt; limit; ++i)
{
    bool isLast = i == limit - 1;
    try
    {
        using (var db = /* ... */)
        {
            resultSet = db.RunStoredProcedure(param1, param2).ToList();
        }
        //if it gets here it was successful
        break;
    }
    catch (SqlException ex)
    {
        if (isLast)
        {
            //3 transient errors in a row. So just kill it
            throw;
        }
        switch (ex.Number)
        {
            case 1205: //deadlock
            case -2:   //timeout (command timeout?)
            case 11:   //timeout (connection timeout?)
                // do nothing - continue the loop
                break;
            default:
                //a non-transient error. Just throw the exception on
                throw;
        }
    }
    Thread.Sleep(TimeSpan.FromSeconds(1)); //some kind of delay - might not use Sleep
}
return resultSet;
</code></pre>

<p>(excuse me for any bugs - I've just written that on the fly. I also realise I could wrap it up nicely...)</p>

<p>So the key question is: what numbers should I consider 'transient' (I realise what I consider transient may be different to what other people consider transient). I've found a nice list here:</p>

<p><a href="https://msdn.microsoft.com/en-us/library/cc645603.aspx" rel="noreferrer">https://msdn.microsoft.com/en-us/library/cc645603.aspx</a></p>

<p>but its massive and note very useful. <strong>Has anyone else built up a list that they use for something similar?</strong></p>

<p><strong>UPDATE</strong></p>

<p>In the end, we opted for a 'bad list' - if the error is a one of a list of known 'non-transient errors' - which are generally programmer errors. I've included a list of numbers we are using as an answer.</p>