排查 SQL 数据库和 SQL 托管实例中的临时连接错误Troubleshoot transient connection errors in SQL Database and SQL Managed Instance

适用于: 是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics (SQL DW) APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance yes Azure Synapse Analytics (SQL DW)

本文介绍如何防止、排查、诊断和减少客户端应用程序在与 Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 交互时发生的连接错误和暂时性错误。This article describes how to prevent, troubleshoot, diagnose, and mitigate connection errors and transient errors that your client application encounters when it interacts with Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. 了解如何配置重试逻辑、生成连接字符串以及调整其他连接设置。Learn how to configure retry logic, build the connection string, and adjust other connection settings.

暂时性错误(暂时性故障)Transient errors (transient faults)

暂时性错误(也称为暂时性故障)的根本原因很快就能自行解决。A transient error, also known as a transient fault, has an underlying cause that soon resolves itself. 当 Azure 系统快速地将硬件资源转移到负载均衡更好的各种工作负荷时,偶尔会发生暂时性错误。An occasional cause of transient errors is when the Azure system quickly shifts hardware resources to better load-balance various workloads. 大多数这些重新配置事件在 60 秒内就能完成。Most of these reconfiguration events finish in less than 60 seconds. 在进行这种重新配置的过程中,可能会在 SQL 数据库中连接到你的数据库时遇到问题。During this reconfiguration time span, you might have issues with connecting to your database in SQL Database. 连接到你的数据库的应用程序应当构建为能预见这些暂时性错误。Applications that connect to your database should be built to expect these transient errors. 为了处理这些错误,可应用程序代码中实现重试逻辑,而不是以应用程序错误的形式呈现给用户。To handle them, implement retry logic in their code instead of surfacing them to users as application errors.

如果客户端程序使用 ADO.NET,系统会引发 SqlException,使程序知道已发生暂时性错误。If your client program uses ADO.NET, your program is told about the transient error by the throw of SqlException.

连接与命令Connection vs. command

重试 SQL 数据库和 SQL 托管实例连接或重新建立连接,具体取决于以下各项:Retry the SQL Database and SQL Managed Instance connection or establish it again, depending on the following:

  • 尝试连接期间发生暂时性错误A transient error occurs during a connection try

延迟几秒钟后,重试连接。After a delay of several seconds, retry the connection.

  • 在 SQL 数据库和 SQL 托管实例查询命令期间遇到暂时性错误A transient error occurs during a SQL Database and SQL Managed Instance query command

不要立即重试该命令,Do not immediately retry the command. 应在一定的延迟之后建立新的连接。Instead, after a delay, freshly establish the connection. 然后重试命令。Then retry the command.

针对暂时性错误的重试逻辑Retry logic for transient errors

在偶尔会遇到暂时性错误的客户端程序中包含重试逻辑可以让它变得更稳健。Client programs that occasionally encounter a transient error are more robust when they contain retry logic. 如果你的程序通过第三方中间件与你在 SQL 数据库中的数据库通信,请咨询供应商,了解该中间件是否包含暂时性错误的重试逻辑。When your program communicates with your database in SQL Database through third-party middleware, ask the vendor whether the middleware contains retry logic for transient errors.

重试原则Principles for retry

  • 如果错误是暂时性的,请重试打开连接。If the error is transient, retry to open a connection.
  • 不要直接重试由于暂时性错误而失败的 SQL 数据库或 SQL 托管实例 SELECT 语句。Do not directly retry a SQL Database or SQL Managed Instance SELECT statement that failed with a transient error. 而应建立新的连接,然后重试 SELECTInstead, establish a fresh connection, and then retry the SELECT.
  • SQL 数据库或 SQL 托管实例 UPDATE 语句由于暂时性错误而失败时,请先建立新的连接,然后重试 UPDATE。When a SQL Database or SQL Managed Instance UPDATE statement fails with a transient error, establish a fresh connection before you retry the UPDATE. 重试逻辑必须确保整个数据库事务完成,或整个事务已回滚。The retry logic must ensure that either the entire database transaction finished or that the entire transaction is rolled back.

其他重试注意事项Other considerations for retry

  • 下班后自动启动的批处理程序以及在凌晨之前完成的批处理程序在每次重试前经过较长的时间间隔。A batch program that automatically starts after work hours and finishes before morning can afford to be very patient with long time intervals between its retry attempts.
  • 用户界面程序应该解释用户会在长时间等待后放弃操作的倾向。A user interface program should account for the human tendency to give up after too long a wait. 解决方案不得每隔几秒钟重试,因为该策略可能会使系统填满请求。The solution must not retry every few seconds, because that policy can flood the system with requests.

增大重试间隔Interval increase between retries

我们建议在第一次重试前等待 5 秒。We recommend that you wait 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.

要了解使用 ADO.NET 的客户端的暂停时段,请参阅连接池 (ADO.NET)For a discussion of the blocking period for clients that use ADO.NET, see Connection pooling (ADO.NET).

还可以设置程序在自行终止之前的重试次数上限。You also might want to set a maximum number of retries before the program self-terminates.

重试逻辑代码示例Code samples with retry logic

以下文档提供了有关重试逻辑的代码示例:Code examples with retry logic are available at:

测试重试逻辑Test your retry logic

若要测试重试逻辑,必须模拟或生成程序仍在运行时可更正的错误。To test your retry logic, you must simulate or cause an error that can be corrected while your program is still running.

通过断开网络连接进行测试Test by disconnecting from the network

可以测试重试逻辑的一种方法是在程序运行时断开客户端计算机与网络的连接。One way you can test your retry logic is to disconnect your client computer from the network while the program is running. 错误为:The error is:

  • SqlException.Number = 11001SqlException.Number = 11001
  • 消息:“此主机不存在”Message: "No such host is known"

第一次重试时,可以将客户端计算机重新连接到网络,然后尝试连接。As part of the first retry attempt, you can reconnect your client computer to the network and then attempt to connect.

要使此测试可行,请从网络中断开计算机的连接,再启动程序。To make this test practical, unplug your computer from the network before you start your program. 然后,程序将识别促使它执行以下操作的运行时参数:Then your program recognizes a runtime parameter that causes the program to:

  • 暂时将 11001 添加到视为暂时性故障的错误列表。Temporarily add 11001 to its list of errors to consider as transient.
  • 像往常一样尝试首次连接。Attempt its first connection as usual.
  • 在捕获该错误后,从列表中删除 11001。After the error is caught, remove 11001 from the list.
  • 显示一条消息,告知用户要将计算机接入网络。Display a message that tells the user to plug the computer into the network.
  • 通过使用“Console.ReadLine” 方法或具有“确定”按钮的对话框暂停进一步执行。Pause further execution by using either the Console.ReadLine method or a dialog with an OK button. 将计算机接入网络后,用户按 Enter 键。The user presses the Enter key after the computer is plugged into the network.
  • 重新尝试连接,预期会成功。Attempt again to connect, expecting success.

通过在连接时拼错用户名进行测试Test by misspelling the user name when connecting

在首次连接尝试之前,程序可以故意拼错用户名。Your program can purposely misspell the user name before the first connection attempt. 错误为:The error is:

  • SqlException.Number = 18456SqlException.Number = 18456
  • 消息:“用户 'WRONG_MyUserName' 的登录失败。”Message: "Login failed for user 'WRONG_MyUserName'."

在首次重试过程中,程序可以更正拼写错误,并尝试连接。As part of the first retry attempt, your program can correct the misspelling and then attempt to connect.

要使此测试可行,程序需识别促使它执行以下操作的运行时参数:To make this test practical, your program recognizes a runtime parameter that causes the program to:

  • 暂时将 18456 添加到视为暂时性故障的错误列表。Temporarily add 18456 to its list of errors to consider as transient.
  • 故意将“WRONG_”添加到用户名。Purposely add 'WRONG_' to the user name.
  • 在捕获该错误后,从列表中删除 18456。After the error is caught, remove 18456 from the list.
  • 从用户名中删除“WRONG_”。Remove 'WRONG_' from the user name.
  • 重新尝试连接,预期会成功。Attempt again to connect, expecting success.

连接重试的 .NET SqlConnection 参数.NET SqlConnection parameters for connection retry

如果你的客户端程序使用 .NET Framework 类 System.Data.SqlClient.SqlConnection 连接到你在 SQL 数据库中的数据库,请使用 .NET 4.6.1 或更高版本(或 .NET Core),以便利用其连接重试功能。If your client program connects to your database in SQL Database by using the .NET Framework class System.Data.SqlClient.SqlConnection, use .NET 4.6.1 or later (or .NET Core) so that you can use its connection retry feature. 有关此功能的详细信息,请参阅此网页For more information on the feature, see this webpage.

SqlConnection 对象生成连接字符串时,请在以下参数之间协调值:When you build the connection string for your SqlConnection object, coordinate the values among the following parameters:

  • ConnectRetryCount:  默认值为 1。ConnectRetryCount:  Default is 1. 范围为 0 到 255。Range is 0 through 255.
  • ConnectRetryInterval:  默认值为 10 秒。ConnectRetryInterval:  Default is 10 seconds. 范围为 1 到 60。Range is 1 through 60.
  • Connection Timeout:  默认值为 15 秒。Connection Timeout:  Default is 15 seconds. 范围为 0 到 2147483647。Range is 0 through 2147483647.

具体而言,所选的值应使以下等式成立:连接超时值 = ConnectRetryCount * ConnectionRetryIntervalSpecifically, your chosen values should make the following equality true: Connection Timeout = ConnectRetryCount * ConnectionRetryInterval

例如,如果计数等于 3 且间隔等于 10 秒,超时值仅为 29 秒未给系统足够的时间进行其第三次也是最后一次连接重试,因为 29 < 3 * 10。For example, if the count equals 3 and the interval equals 10 seconds, a timeout of only 29 seconds doesn't give the system enough time for its third and final retry to connect: 29 < 3 * 10.

连接与命令Connection vs. command

ConnectRetryCountConnectRetryInterval 参数使 SqlConnection 对象在重试连接操作时不用通知或麻烦你的程序(例如,将控制权返还给你的程序)。The ConnectRetryCount and ConnectRetryInterval parameters let your SqlConnection object retry the connect operation without telling or bothering your program, such as returning control to your program. 在以下情况下可能会进行重试:The retries can occur in the following situations:

  • SqlConnection.Open 方法调用SqlConnection.Open method call
  • SqlConnection.Execute 方法调用SqlConnection.Execute method call

有个很微妙的地方。There is a subtlety. 如果正在执行查询时发生暂时性错误,SqlConnection 对象不会重试连接操作。If a transient error occurs while your query is being executed, your SqlConnection object doesn't retry the connect operation. 肯定不会重试查询。It certainly doesn't retry your query. 但是, SqlConnection 在发送要执行的查询前会非常快速地检查连接。However, SqlConnection very quickly checks the connection before sending your query for execution. 如果快速检查检测到连接问题, SqlConnection 会重试连接操作。If the quick check detects a connection problem, SqlConnection retries the connect operation. 如果重试成功,则会发送查询以执行。If the retry succeeds, your query is sent for execution.

ConnectRetryCount 是否应结合应用程序重试逻辑?Should ConnectRetryCount be combined with application retry logic

假设应用程序具有功能强大的自定义重试逻辑。Suppose your application has robust custom retry logic. 它可能会重试连接操作四次。It might retry the connect operation four times. 如果你将 ConnectRetryIntervalConnectRetryCount =3 添加到连接字符串,则将重试计数提高到 4 * 3 = 12 次重试。If you add ConnectRetryInterval and ConnectRetryCount =3 to your connection string, you will increase the retry count to 4 * 3 = 12 retries. 可能未打算重试这么多次。You might not intend such a high number of retries.

连接到你在 SQL 数据库中的数据库Connections to your database in SQL Database

连接:连接字符串Connection: Connection string

连接到你的数据库所需的连接字符串与连接到 SQL Server 所需的字符串稍有不同。The connection string that's necessary to connect to your database is slightly different from the string used to connect to SQL Server. 可以通过 Azure 门户复制数据库的连接字符串。You can copy the connection string for your database from the Azure portal.

从 Azure 门户获取连接字符串Obtain the connection string from the Azure portal

使用 Azure 门户获取客户端程序与 Azure SQL 数据库进行交互所需的连接字符串。Use the Azure portal to obtain the connection string that's necessary for your client program to interact with Azure SQL Database.

  1. 选择“所有服务” > “SQL 数据库” 。Select All services > SQL databases.

  2. 在“SQL 数据库”边栏选项卡左上角附近的筛选器文本框中输入数据库的名称。 Enter the name of your database into the filter text box near the upper left of the SQL databases blade.

  3. 选择数据库所对应的行。Select the row for your database.

  4. 显示数据库的边栏选项卡后,为了方便查看,请选择“最小化”按钮 ,以便折叠用于浏览和数据库筛选的边栏选项卡。After the blade appears for your database, for visual convenience select the Minimize buttons to collapse the blades you used for browsing and database filtering.

  5. 在数据库边栏选项卡上,选择“显示数据库连接字符串”。 On the blade for your database, select Show database connection strings.

  6. 复制相应的连接字符串。Copy the appropriate connection string. 也就是说,如果想要使用 ADO.NET 连接库,则从“ADO.NET” 选项卡中复制相应的字符串。i.e. If you intend to use the ADO.NET connection library, copy the appropriate string from the ADO.NET tab.

    复制数据库的 ADO 连接字符串

  7. 根据需要编辑连接字符串。Edit the connection string as needed. 即,向连接字符串中插入密码,或者在用户名或服务器名称太长的情况下,从用户名中删除“@<servername>”。i.e. Insert your password into the connection string, or remove "@<servername>" from the username if the username or server name are too long.

  8. 通过这种或那种格式,将连接字符串信息粘贴到客户端程序代码中。In one format or another, paste the connection string information into your client program code.

有关详细信息,请参阅连接字符串和配置文件For more information, see Connection strings and configuration files.

连接:IP 地址Connection: IP address

必须配置 SQL 数据库,使其接受来自托管你的客户端程序的计算机 IP 地址的通信。You must configure SQL Database to accept communication from the IP address of the computer that hosts your client program. 若要设置此配置,可以通过 Azure 门户编辑防火墙设置。To set up this configuration, edit the firewall settings through the Azure portal.

如果忘记了配置 IP 地址,程序会失败,并显示简单的错误消息,指出所需的 IP 地址。If you forget to configure the IP address, your program fails with a handy error message that states the necessary IP address.

  1. 登录 Azure 门户Sign in to the Azure portal.

  2. 在左侧的列表中,选择“所有服务” 。In the list on the left, select All services.

  3. 滚动并选择“SQL Server”。 Scroll and select SQL servers.

    在门户中找到 Azure SQL 数据库服务器

  4. 在筛选器文本框中,开始键入服务器的名称。In the filter text box, start typing the name of your server. 此时会显示行。Your row is displayed.

  5. 选择服务器所对应的行。Select the row for your server. 此时会显示服务器的边栏选项卡。A blade for your server is displayed.

  6. 在服务器边栏选项卡上选择“设置”。 On your server blade, select Settings.

  7. 选择“防火墙” 。Select Firewall.

    选择“设置”>“防火墙”

  8. 选择“添加客户端 IP”。 Select Add Client IP. 在第一个文本框中键入新规则的名称。Type a name for your new rule in the first text box.

  9. 键入要启用的范围的下限和上限 IP 地址值。Type in the low and high IP address values for the range you want to enable.

    • 为方便起见,可以让下限值以 .0 结尾,让上限值以 .255 结尾。It can be handy to have the low value end with .0 and the high value end with .255.
  10. 选择“保存”。 Select Save.

有关详细信息,请参阅在 SQL 数据库中配置防火墙设置For more information, see Configure firewall settings in SQL Database.

连接:端口Connection: Ports

通常,只需确保在托管客户端程序的计算机上已打开端口 1433 进行出站通信。Typically, you need to ensure that only port 1433 is open for outbound communication on the computer that hosts your client program.

例如,当客户端程序托管在 Windows 计算机上时,则可以使用主机上的 Windows 防火墙打开端口 1433。For example, when your client program is hosted on a Windows computer, you can use Windows Firewall on the host to open port 1433.

  1. 打开控制面板。Open Control Panel.
  2. 选择“所有控制面板项” > “Windows 防火墙” > “高级设置” > “出站规则” > “操作” > “新建规则”。 Select All Control Panel Items > Windows Firewall > Advanced Settings > Outbound Rules > Actions > New Rule.

如果客户端程序托管在 Azure 虚拟机 (VM) 上,请阅读用于 ADO.NET 4.5 和 SQL 数据库的非 1433 端口If your client program is hosted on an Azure virtual machine (VM), read Ports beyond 1433 for ADO.NET 4.5 and SQL Database.

有关在数据库中配置端口和 IP 地址的背景信息,请参阅 Azure SQL 数据库防火墙For background information about configuration of ports and IP addresses in your database, see Azure SQL Database firewall.

连接:ADO.NET 4.6.2 或更高版本Connection: ADO.NET 4.6.2 or later

如果程序使用 System.Data.SqlClient.SqlConnection 等 ADO.NET 类来连接到 SQL 数据库,我们建议使用 .NET Framework 4.6.2 或更高版本。If your program uses ADO.NET classes like System.Data.SqlClient.SqlConnection to connect to SQL Database, we recommend that you use .NET Framework version 4.6.2 or later.

从 ADO.NET 4.6.2 开始Starting with ADO.NET 4.6.2

  • 将立即为 Azure SQL 重试连接打开尝试,从而提升支持云的应用的性能。The connection open attempt to be retried immediately for Azure SQL, thereby improving the performance of cloud-enabled apps.

从 ADO.NET 4.6.1 开始Starting with ADO.NET 4.6.1

  • 对于 SQL 数据库,使用 SqlConnection.Open 方法打开连接可以获得更高的可靠性。For SQL Database, reliability is improved when you open a connection by using the SqlConnection.Open method. Open 方法现在结合了应对暂时性故障的最佳效果重试机制,用于处理连接超时期间发生的特定错误。The Open method now incorporates best-effort retry mechanisms in response to transient faults for certain errors within the connection timeout period.
  • 支持连接池,其中包括有效验证提供给程序的连接对象是否正常运行的功能。Connection pooling is supported, which includes an efficient verification that the connection object it gives your program is functioning.

若要从连接池使用连接对象,我们建议,如果程序未立即使用连接,应暂时关闭连接。When you use a connection object from a connection pool, we recommend that your program temporarily closes the connection when it's not immediately in use. 重新打开连接的开销并不高,但要创建新连接。It's not expensive to reopen a connection, but it is to create a new connection.

如果使用 ADO.NET 4.0 或更低版本,我们建议升级到最新的 ADO.NET。If you use ADO.NET 4.0 or earlier, we recommend that you upgrade to the latest ADO.NET. 从 2018 年 8 月开始,可以下载 ADO.NET 4.6.2As of August 2018, you can download ADO.NET 4.6.2.

诊断Diagnostics

诊断:测试实用程序是否可以连接Diagnostics: Test whether utilities can connect

如果你程序无法连接到你在 SQL 数据库中的数据库,可通过一个诊断选项尝试使用某个实用程序进行连接。If your program fails to connect to your database in SQL Database, one diagnostic option is to try to connect with a utility program. 理想的情况下,该实用工具将使用程序所用的同一个库进行连接。Ideally, the utility connects by using the same library that your program uses.

可以在任何 Windows 计算机上尝试以下实用程序:On any Windows computer, you can try these utilities:

  • SQL Server Management Studio (ssms.exe),它使用 ADO.NET 进行连接SQL Server Management Studio (ssms.exe), which connects by using ADO.NET
  • sqlcmd.exe,它使用 ODBC 进行连接sqlcmd.exe, which connects by using ODBC

连接程序后,测试一个简短的 SQL SELECT 查询是否可以正常工作。After your program is connected, test whether a short SQL SELECT query works.

诊断:检查打开的端口Diagnostics: Check the open ports

如果你怀疑连接尝试由于端口问题而失败,可以在计算机上运行报告端口配置的实用工具。If you suspect that connection attempts fail due to port issues, you can run a utility on your computer that reports on the port configurations.

在 Linux 上,以下实用工具可能很有用:On Linux, the following utilities might be helpful:

  • netstat -nap
  • nmap -sS -O 127.0.0.1:请将示例值更改为你的 IP 地址。nmap -sS -O 127.0.0.1: Change the example value to be your IP address.

在 Windows 上,PortQry.exe 实用工具可能很有用。On Windows, the PortQry.exe utility might be helpful. 下面是一个示例执行,它在 SQL 数据库中某个数据库上查询端口情况,并在笔记本电脑上运行:Here's an example execution that queried the port situation on a database in SQL Database and that was run on a laptop computer:

[C:\Users\johndoe\]
>> portqry.exe -n johndoesvr9.database.chinacloudapi.cn -p tcp -e 1433

Querying target system called: johndoesvr9.database.chinacloudapi.cn

Attempting to resolve name to IP address...
Name resolved to 23.100.117.95

querying...
TCP port 1433 (ms-sql-s service): LISTENING

[C:\Users\johndoe\]
>>

诊断:记录错误Diagnostics: Log your errors

有时,诊断间歇性问题的最好方式是每隔数天或数周检测常规模式。An intermittent problem is sometimes best diagnosed by detection of a general pattern over days or weeks.

客户端可以通过记录其所遇到的所有错误来帮助你进行诊断。Your client can assist in a diagnosis by logging all errors it encounters. 可将日志条目与 SQL 数据库本身内部记录的错误数据相关联。You might be able to correlate the log entries with error data that SQL Database logs itself internally.

Enterprise Library 6 (EntLib60) 提供了 .NET 托管类来帮助进行日志记录。Enterprise Library 6 (EntLib60) offers .NET managed classes to assist with logging. 有关详细信息,请参阅:5 - 与写入日志一样简单:使用日志记录应用程序块For more information, see 5 - As easy as falling off a log: Use the Logging Application Block.

诊断:在系统日志中检查错误Diagnostics: Examine system logs for errors

下面是查询错误日志和其他信息的一些 Transact-SQL SELECT 语句。Here are some Transact-SQL SELECT statements that query error logs and other information.

日志查询Query of log 说明Description
SELECT e.*
FROM sys.event_log AS e
WHERE e.database_name = 'myDbName'
AND e.event_category = 'connectivity'
AND 2 >= DateDiff
  (hour, e.end_time, GetUtcDate())
ORDER BY e.event_category,
  e.event_type, e.end_time;
sys.event_log 视图提供有关各个事件的信息,包括一些可能导致暂时性错误或连接故障的事件。The sys.event_log view offers information about individual events, which includes some that can cause transient errors or connectivity failures.

理想情况下,可以将 start_timeend_time 值与有关客户端程序遇到问题时的信息相关联。Ideally, you can correlate the start_time or end_time values with information about when your client program experienced problems.

必须连接到 master 数据库才能运行此查询。You must connect to the master database to run this query.
SELECT c.*
FROM sys.database_connection_stats AS c
WHERE c.database_name = 'myDbName'
AND 24 >= DateDiff
  (hour, c.end_time, GetUtcDate())
ORDER BY c.end_time;
sys.database_connection_stats 视图针对其他诊断提供事件类型的聚合计数。The sys.database_connection_stats view offers aggregated counts of event types for additional diagnostics.

必须连接到 master 数据库才能运行此查询。You must connect to the master database to run this query.

诊断:在 SQL 数据库日志中搜索问题事件Diagnostics: Search for problem events in the SQL Database log

可以在 SQL 数据库日志中搜索有关问题事件的条目。You can search for entries about problem events in the SQL Database log. master 数据库中尝试运行以下 Transact-SQL SELECT 语句:Try the following Transact-SQL SELECT statement in the master database:

SELECT
   object_name
  ,CAST(f.event_data as XML).value
      ('(/event/@timestamp)[1]', 'datetime2')                      AS [timestamp]
  ,CAST(f.event_data as XML).value
      ('(/event/data[@name="error"]/value)[1]', 'int')             AS [error]
  ,CAST(f.event_data as XML).value
      ('(/event/data[@name="state"]/value)[1]', 'int')             AS [state]
  ,CAST(f.event_data as XML).value
      ('(/event/data[@name="is_success"]/value)[1]', 'bit')        AS [is_success]
  ,CAST(f.event_data as XML).value
      ('(/event/data[@name="database_name"]/value)[1]', 'sysname') AS [database_name]
FROM
  sys.fn_xe_telemetry_blob_target_read_file('el', null, null, null) AS f
WHERE
  object_name != 'login_event'  -- Login events are numerous.
  and
  '2015-06-21' < CAST(f.event_data as XML).value
        ('(/event/@timestamp)[1]', 'datetime2')
ORDER BY
  [timestamp] DESC
;

将返回 sys.fn_xe_telemetry_blob_target_read_file 中的若干行A few returned rows from sys.fn_xe_telemetry_blob_target_read_file

以下示例显示返回的行的类似内容。The following example shows what a returned row might look like. 显示的 null 值在其他行中通常不是 null。The null values shown are often not null in other rows.

object_name                   timestamp                    error  state  is_success  database_name

database_xml_deadlock_report  2015-10-16 20:28:01.0090000  NULL   NULL   NULL        AdventureWorks

Enterprise Library 6Enterprise Library 6

Enterprise Library 6 (EntLib60) 是 .NET 类的框架,可帮助你实施云服务(包括 SQL 数据库)的可靠客户端。Enterprise Library 6 (EntLib60) is a framework of .NET classes that helps you implement robust clients of cloud services, one of which is SQL Database. 若要查找 EntLib60 可以提供帮助的各个领域的相关专题,请参阅 Enterprise Library 6 - 2013 年 4 月To locate topics dedicated to each area in which EntLib60 can assist, see Enterprise Library 6 - April 2013.

用于处理暂时性错误的重试逻辑是 EntLib60 可以提供帮助的一个领域。Retry logic for handling transient errors is one area in which EntLib60 can assist. 有关详细信息,请参阅 4 - 锲而不舍是一切成功的秘密:使用暂时性故障处理应用程序块For more information, see 4 - Perseverance, secret of all triumphs: Use the Transient Fault Handling Application Block.

备注

EntLib60 的源代码可从下载中心公开下载。The source code for EntLib60 is available for public download from the Download Center. Microsoft 不打算对 EntLib 做进一步的功能更新或维护更新。Microsoft has no plans to make further feature updates or maintenance updates to EntLib.

用于暂时性错误和重试的 EntLib60 类EntLib60 classes for transient errors and retry

以下 EntLib60 类对重试逻辑特别有用。The following EntLib60 classes are particularly useful for retry logic. 可以在 Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling 命名空间或其子级中找到所有这些类。All these classes are found in or under the namespace Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.

在命名空间 Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling 中:In the namespace Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling:

  • RetryPolicyRetryPolicy class
    • ExecuteAction 方法ExecuteAction method
  • ExponentialBackoffExponentialBackoff class
  • SqlDatabaseTransientErrorDetectionStrategySqlDatabaseTransientErrorDetectionStrategy class
  • ReliableSqlConnectionReliableSqlConnection class
    • ExecuteCommand 方法ExecuteCommand method

在命名空间 Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.TestSupport中:In the namespace Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.TestSupport:

  • AlwaysTransientErrorDetectionStrategyAlwaysTransientErrorDetectionStrategy class
  • NeverTransientErrorDetectionStrategyNeverTransientErrorDetectionStrategy class

以下是 EntLib60 相关信息的某些链接:Here are some links to information about EntLib60:

EntLib60:日志记录块EntLib60: The logging block

  • 日志记录块是极其灵活且可配置的解决方案,可用于:The logging block is a highly flexible and configurable solution that you can use to:
    • 创建日志消息并将其存储在各种不同的位置。Create and store log messages in a wide variety of locations.
    • 分类与筛选消息。Categorize and filter messages.
    • 收集有助于调试和跟踪的上下文信息,以及用于满足审核和一般日志记录要求的上下文信息。Collect contextual information that is useful for debugging and tracing, as well as for auditing and general logging requirements.
  • 日志记录块可以从日志目标抽象化日志记录功能,使应用程序代码保持一致,无论目标日志记录存储的位置和类型为何。The logging block abstracts the logging functionality from the log destination so that the application code is consistent, irrespective of the location and type of the target logging store.

有关详细信息,请参阅:5 - 与写入日志一样简单:使用日志记录应用程序块For more information, see 5 - As easy as falling off a log: Use the Logging Application Block.

EntLib60 IsTransient 方法的源代码EntLib60 IsTransient method source code

接下来,SqlDatabaseTransientErrorDetectionStrategy 类包含 IsTransient 方法的 C# 源代码。Next, from the SqlDatabaseTransientErrorDetectionStrategy class, is the C# source code for the IsTransient method. 该源代码阐明了哪些错误被视为暂时性错误并值得重试(截止 2013 年 4 月)。The source code clarifies which errors were considered transient and worthy of retry, as of April 2013.

public bool IsTransient(Exception ex)
{
  if (ex != null)
  {
    SqlException sqlException;
    if ((sqlException = ex as SqlException) != null)
    {
      // Enumerate through all errors found in the exception.
      foreach (SqlError err in sqlException.Errors)
      {
        switch (err.Number)
        {
            // SQL Error Code: 40501
            // The service is currently busy. Retry the request after 10 seconds.
            // Code: (reason code to be decoded).
          case ThrottlingCondition.ThrottlingErrorNumber:
            // Decode the reason code from the error message to
            // determine the grounds for throttling.
            var condition = ThrottlingCondition.FromError(err);

            // Attach the decoded values as additional attributes to
            // the original SQL exception.
            sqlException.Data[condition.ThrottlingMode.GetType().Name] =
              condition.ThrottlingMode.ToString();
            sqlException.Data[condition.GetType().Name] = condition;

            return true;

          case 10928:
          case 10929:
          case 10053:
          case 10054:
          case 10060:
          case 40197:
          case 40540:
          case 40613:
          case 40143:
          case 233:
          case 64:
            // DBNETLIB Error Code: 20
            // The instance of SQL Server you attempted to connect to
            // does not support encryption.
          case (int)ProcessNetLibErrorCode.EncryptionNotSupported:
            return true;
        }
      }
    }
    else if (ex is TimeoutException)
    {
      return true;
    }
    else
    {
      EntityException entityException;
      if ((entityException = ex as EntityException) != null)
      {
        return this.IsTransient(entityException.InnerException);
      }
    }
  }

  return false;
}

后续步骤Next steps