What SQL Error codes should be allowed to retry?

Question

We have a SQL Server 2014 AlwaysOn Availability Group that we connect to via standard C# SqlConnection objects. We want to catch the exceptions that are happening when a failover is triggered either manually or automatically and retry the command after a period of waiting.

We don't just want to retry every command that fails, just the transactions that failed because of a failover.

Does Microsoft produce a list (or is there a list somewhere) of error codes that we can check for?

Answer 1

Microsoft has build a Transient Error Detection Strategy for Azure once to use in the Transient Fault Handling Application Block.

The Transient Fault Handling Application Block (Topaz) makes your application more robust by providing the logic for handling transient faults. It does this in two ways.

First, the block includes logic to identify transient faults for a number of common cloud-based services in the form of detection strategies. These detection strategies contain built-in knowledge that is capable of identifying whether a particular exception is likely to be caused by a transient fault condition.

They have identified sql error codes that indicate a transient failure. While it is aimed for the cloud (for Azure Sql Server) we have successfully used it in our own on-premises environment.

You can take a look at their code and extract the sql codes you can check for:

https://github.com/Dixin/EnterpriseLibrary.TransientFaultHandling.Core/blob/master/Source/TransientFaultHandling.Data.Core/SqlDatabaseTransientErrorDetectionStrategy.cs

Answer 2

It's for ms sql 2000, but probably the "error levels" are the same. I hope, it will help you

http://www.developer.com/tech/article.php/724711/Microsoft-SQL-Server-2000-Error-Messages.htm

EDIT:

Here's the documentation about error levels in msdn: (sql2014) https://msdn.microsoft.com/en-us/library/ms164086(v=sql.120).aspx