title: Sql Server Transient Exception Numbers
body: <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>