Menu
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!
931
There are two types of errors in SQL Server: system errors and custom errors. System errors can be viewed in the sys. messages system view and are defined by SQL server. Therefore, when a system error occurs, SQL Server will log a system error and may take actions to fix the error.
Returns 0 if the previous Transact-SQL statement encountered no errors. Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys. messages catalog view, then @@ERROR contains the value from the sys.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
