当前位置：文江博客话题详情

应重试的 SQL Server 错误列表？

发布于 2024-08-16 12:54:49 字数 150 浏览 3 评论 0原文

是否有一个简洁的 SQL Server 存储过程错误列表，可以自动重试？显然，重试“登录失败”错误没有意义，但重试“超时”则有意义。我认为指定要重试的错误可能比指定不重试的错误更容易。

那么，除了“超时”错误之外，还有哪些其他错误适合自动重试呢？

谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

灵芸 2024-08-23 12:54:49

您应该重试（重新运行）整个事务，而不仅仅是单个查询/SP。
至于重试的错误，我一直在使用以下列表：

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,

最重要的当然是“死锁受害者”错误1205。

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.

回复收藏 0 原文

很快妥协 2024-08-23 12:54:49

如果您想要绝对完整的列表，我会扩展该列表，使用查询并过滤结果。

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

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

回复收藏 0 原文

浅听莫相离 2024-08-23 12:54:49

您可以使用 SQL 查询来查找显式请求重试的错误（尝试排除那些也需要其他操作的错误）。

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%'

以下是错误代码列表：
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 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.

回复收藏 0 原文