azure不支持哪些语句 sql_排查 Azure SQL 数据库的常见连接问题 - Azure SQL Database | Microsoft Docs...

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

排查 Azure SQL 数据库和 Azure SQL 托管实例的连接问题和其他问题Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance

01/14/2020

本文内容

适用于：

Azure SQL 数据库

Azure SQL 托管实例

与 Azure SQL 数据库或 Azure SQL 托管实例连接失败时，你会收到错误消息。You receive error messages when the connection to Azure SQL Database or Azure SQL Managed Instance fails. 连接问题可能由以下原因导致：重新配置、防火墙设置、连接超时、登录信息不正确，或无法在应用程序设计过程中应用最佳做法和设计准则。These connection problems can be caused by reconfiguration, firewall settings, a connection timeout, incorrect login information or failure to apply best practices and design guidelines during the application design process. 此外，如果达到了某些 Azure SQL 数据库或 SQL 托管实例资源的最大限制，则无法再连接。Additionally, if the maximum limit on some Azure SQL Database or SQL Managed Instance resources is reached, you can no longer connect.

暂时性故障错误消息(40197、40613 等)Transient fault error messages (40197, 40613 and others)

Azure 基础结构能够在 SQL 数据库服务中出现大量工作负荷时动态地重新配置服务器。The Azure infrastructure has the ability to dynamically reconfigure servers when heavy workloads arise in the SQL Database service. 此动态行为可能会导致客户端程序失去其与数据库或实例的连接。This dynamic behavior might cause your client program to lose its connection to the database or instance. 此类错误情况称为 暂时性故障 。This kind of error condition is called a transient fault . 之所以会发生数据库重新配置事件是因为，有计划内事件(例如，软件升级)或计划外事件(例如，进程故障或负载均衡)。Database reconfiguration events occur because of a planned event (for example, a software upgrade) or an unplanned event (for example, a process crash, or load balancing). 大多数重新配置事件的生存期通常较短，应在最多 60 秒内完成。Most reconfiguration events are generally short-lived and should be completed in less than 60 seconds at most. 但是，这些事件偶尔可能需要更长时间才能完成，例如当大型事务导致长时间运行的恢复时。However, these events can occasionally take longer to finish, such as when a large transaction causes a long-running recovery. 下表列出了在连接到 SQL 数据库时应用程序可能会收到的各种暂时性错误The following table lists various transient errors that applications can receive when connecting to SQL Database

暂时性故障错误代码的列表List of transient fault error codes

错误代码Error code

严重性Severity

说明Description

40604060

1616

无法打开该登录请求的数据库“%.*ls”。Cannot open database "%.*ls" requested by the login. 登录失败。The login failed. For more information, see Errors 4000 to 4999

4019740197

1717

该服务在处理你的请求时遇到错误。The service has encountered an error processing your request. 请重试。Please try again. 错误代码 %d。Error code %d.

当服务由于软件或硬件升级、硬件故障或任何其他故障转移问题而关闭时，将收到此错误。You receive this error when the service is down due to software or hardware upgrades, hardware failures, or any other failover problems. 错误 40197 的消息中嵌入的错误代码 (%d) 提供有关所发生的故障或故障转移类型的其他信息。The error code (%d) embedded within the message of error 40197 provides additional information about the kind of failure or failover that occurred. 错误 40197 的消息中嵌入的错误代码的一些示例有 40020、40143、40166 和 40540。Some examples of the error codes are embedded within the message of error 40197 are 40020, 40143, 40166, and 40540.

重新连接会将你自动连接到数据库的正常运行副本。Reconnecting automatically connects you to a healthy copy of your database. 应用程序必须捕获错误 40197、记录该消息中嵌入的错误代码 (%d) 以供进行故障排除，然后尝试重新连接到 SQL 数据库，直到资源可用且再次建立连接为止。Your application must catch error 40197, log the embedded error code (%d) within the message for troubleshooting, and try reconnecting to SQL Database until the resources are available, and your connection is established again. 有关详细信息，请参阅暂时性错误。For more information, see Transient errors.

4061340613

1717

数据库“%.*ls”(在服务器“%.*ls”上)当前不可用。Database '%.*ls' on server '%.*ls' is not currently available. 请稍后重试连接。Please retry the connection later. 如果问题仍然存在，请与客户支持人员联系，并向其提供“%.*ls”的会话跟踪 ID。If the problem persists, contact customer support, and provide them the session tracing ID of '%.*ls'.

如果已建立到数据库的现有专用管理员连接 (DAC)，则可能发生此错误。This error may occur if there is already an existing dedicated administrator connection (DAC) established to the database. 有关详细信息，请参阅暂时性错误。For more information, see Transient errors.

4991949919

1616

无法处理创建或更新请求。Cannot process create or update request. 订阅“%ld”有太多创建或更新操作正在进行。Too many create or update operations in progress for subscription "%ld".

服务正忙于为订阅或服务器处理多个创建或更新请求。The service is busy processing multiple create or update requests for your subscription or server. 为了优化资源，当前阻止了请求。Requests are currently blocked for resource optimization. Query sys.dm_operation_status for pending operations. 请等到挂起的创建或更新请求完成后，或删除其中一个挂起的请求，再重试请求。Wait until pending create or update requests are complete or delete one of your pending requests and retry your request later. 有关详细信息，请参阅：For more information, see:

4992049920

1616

无法处理请求。Cannot process request. 订阅“%ld”有太多操作正在进行。Too many operations in progress for subscription "%ld".

服务正忙于为此订阅处理多个请求。The service is busy processing multiple requests for this subscription. 为了优化资源，当前阻止了请求。Requests are currently blocked for resource optimization. Query sys.dm_operation_status for operation status. 请等到挂起的请求完成，或删除其中一个挂起的请求，然后重试请求。Wait until pending requests are complete or delete one of your pending requests and retry your request later. 有关详细信息，请参阅：For more information, see:

42214221

1616

由于等待“HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING”的时间过长，登录以读取次要副本失败。Login to read-secondary failed due to long wait on 'HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING'. 副本不可用于登录，因为回收副本时缺少正在进行中的事务的行版本。The replica is not available for login because row versions are missing for transactions that were in-flight when the replica was recycled. 可以通过回滚或提交主要副本上的活动事务来解决此问题。The issue can be resolved by rolling back or committing the active transactions on the primary replica. 通过避免在主要副本上长时间写入事务，可以将此状况的发生次数降到最低。Occurrences of this condition can be minimized by avoiding long write transactions on the primary.

解决暂时性连接问题的步骤Steps to resolve transient connectivity issues

查看 Microsoft Azure 服务仪表板以了解应用程序报告错误时出现的任何已知中断。Check the Microsoft Azure Service Dashboard for any known outages that occurred during the time during which the errors were reported by the application.

连接到云服务的应用程序(如 Azure SQL 数据库)应期望定期重新配置事件并实施重试逻辑来处理这些错误，而不是将它们作为应用程序错误展现给用户。Applications that connect to a cloud service such as Azure SQL Database should expect periodic reconfiguration events and implement retry logic to handle these errors instead of surfacing these as application errors to users.

由于数据库即将达到其资源限制，因此错误看起来像是暂时性连接问题。As a database approaches its resource limits, it can seem to be a transient connectivity issue. 请参阅资源限制。

如果连接问题继续存在，或者应用程序发生错误的持续时间超过 60 秒或在特定的一天中看到错误多次发生，请通过在 Azure 支持网站上选择“ 获取支持 ”提出 Azure 支持请求。If connectivity problems continue, or if the duration for which your application encounters the error exceeds 60 seconds or if you see multiple occurrences of the error in a given day, file an Azure support request by selecting Get Support on the Azure Support site.

实现重试逻辑Implementing Retry Logic

强烈建议客户端程序包含重试逻辑，以便它可以提供一段时间来让暂时性故障纠正自身，并尝试重建连接。It is strongly recommended that your client program has retry logic so that it could reestablish a connection after giving the transient fault time to correct itself. 我们建议在第一次重试前延迟 5 秒钟。We recommend that you delay for 5 seconds before your first retry. 如果在少于 5 秒的延迟后重试，云服务有超载的风险。Retrying after a delay shorter than 5 seconds risks overwhelming the cloud service. 对于后续的每次重试，延迟应以指数级增大，最大值为 60 秒。For each subsequent retry the delay should grow exponentially, up to a maximum of 60 seconds.

有关重试逻辑的代码示例，请参阅࿱