List of SQL Server errors that should be retried?

Question

Is there a concise list of SQL Server stored procedure errors that make sense to automatically retry? Obviously, retrying a "login failed" error doesn't make sense, but retrying "timeout" does. I'm thinking it might be easier to specify which errors to retry than to specify which errors not to retry.

So, besides "timeout" errors, what other errors would be good candidates for automatic retrying?

Thanks!

Answer 1

I'm not sure about a full listing of these errors, but I can warn you to be VERY careful about retrying queries. Often there's a larger problem afoot when you get errors from SQL, and simply re-running queries will only further compact the issue. For instance, with the timeout error, you typically will have either a network bottleneck, poorly indexed tables, or something on those lines, and re-running the same query will add to the latency of other queries already obviously struggling to execute.

Answer 2

Instead of retrying errors that occur, if you capture them all for now to a table, then revisit that table every so often to see which ones are turning up.

By analysis the data, you might be able to identify which error would be best suited to trap in your stored procedures.

Answer 3

The one sql server error that you should always catch on inserts and updates (and it is quite often missed), is the deadlock error no. 1205

Appropriate action is to retry the INSERT/UPDATE a small number of times.

Answer 4

You should retry (re-run) the entire transaction, not just a single query/SP. As for the errors to retry, I've been using the following list:

DeadlockVictim = 1205,
LockRequestTimeout = 1222,
OutOfMemory = 701,
OutOfLocks = 1204,
TimeoutWaitingForMemoryResource = 8645,

The most important one is of course the "deadlock victim" error 1205.

Answer 5

I would extend that list, if you want absolutely complete list use the query and filter the result.

select * from master.dbo.sysmessages where description like '%memory%'


    int[] errorNums = new int[]
    {
        701, // Out of Memory
        1204, // Lock Issue
        1205, // Deadlock Victim
        1222, // Lock request time out period exceeded.
        7214, // Remote procedure time out of %d seconds exceeded. Remote procedure '%.*ls' is canceled.
        7604, // Full-text operation failed due to a time out.
        7618, // %d is not a valid value for a full-text connection time out.
        8628, // A time out occurred while waiting to optimize the query. Rerun the query.
        8645, // A time out occurred while waiting for memory resources to execute the query. Rerun the query.
        8651, // Low memory condition
    };