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 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
    };

Answer 2

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 3

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,
SnapshotUpdateConflict = 3960,
// I haven't encountered the following 4 errors in practice
// so I've removed these from my own code:
LockRequestTimeout = 1222,
OutOfMemory = 701,
OutOfLocks = 1204,
TimeoutWaitingForMemoryResource = 8645,

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

Answer 4

You can use a SQL query to look for errors explicitly requesting a retry (trying to exclude those that require another action too).

SELECT  error, description
FROM    master.dbo.sysmessages
WHERE   msglangid = 1033
        AND (description LIKE '%try%later.' OR description LIKE '%. rerun the%')
        AND description NOT LIKE '%resolve%'
        AND description NOT LIKE '%and try%'
        AND description NOT LIKE '%and retry%'

Here's the list of error codes: 539, 617, 952, 956, 983, 1205, 1807, 3055, 5034, 5059, 5061, 5065, 8628, 8645, 8675, 10922, 14258, 20689, 25003, 27118, 30024, 30026, 30085, 33115, 33116, 40602, 40642, 40648

You can tweak the query to look for other conditions like timeouts or memory problems, but I'd recommend configuring your timeout length correctly up front, and then backing off slightly in these scenarios.

Answer 5

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.