目前，我所有的 SQL 请求都显示“System.ComponentModel.Win32Exception：等待操作超时”

Currently, All my SQL Request showing "System.ComponentModel.Win32Exception: The wait operation timed out"

提问by user960567

Suddenly, all of sql server requests showing "System.ComponentModel.Win32Exception: The wait operation timed out". What is the quickest way to find the issue?

突然，所有 sql server 请求都显示“System.ComponentModel.Win32Exception：等待操作超时”。找到问题的最快方法是什么？

Stack Trace: 


[Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1767866
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5352418
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +316
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1481
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21

I got the SQl that is causing the blocking issue by,

我得到了导致阻塞问题的 SQl，

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

回答by user960567

Here how I was able to find the issue, First check all open transaction your database,

在这里我是如何找到问题的，首先检查您的数据库中所有打开的事务，

DBCC OPENTRAN ('Databse')

If there is an open transaction then Grab it's SPID and put it inside INPUTBUFFER

如果有一个打开的事务，则抓取它的 SPID 并将其放入 INPUTBUFFER

DBCC INPUTBUFFER (58)

This will give you the actual SQL. If you want,you can kill this transaction,

这将为您提供实际的 SQL。如果你愿意，你可以终止这个事务，

KILL 58

BTW, In my application I can use READ COMMITTED data,

顺便说一句，在我的应用程序中，我可以使用 READ COMMITTED 数据，

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Or

或者

Select * from Products WITH NoLock

Here is another way to find the SQl quickly,

这是另一种快速找到 SQl 的方法，

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

回答by Dhaval

Try to execute this command:

尝试执行此命令：

exec sp_updatestats

回答by Jon Schneider

This type of SQL Server timeout error can occur on an attempt to INSERT or UPDATE a particular table, when a long-running SELECT query is currently executing on that table (depending on the isolation level of the SELECT).

当长时间运行的 SELECT 查询当前正在该表上执行时（取决于 SELECT 的隔离级别），这种类型的 SQL Server 超时错误可能发生在尝试插入或更新特定表时。

Related:

有关的：

• SQL Server - does [SELECT] lock [UPDATE]?
• When should you use "with (nolock)"

• SQL Server - [SELECT] 锁定 [UPDATE] 吗？
• 什么时候应该使用“with (nolock)”

回答by user919426

For our case, issue was due to the following reasons:

对于我们的案例，问题是由于以下原因：

1. A legacy application running .Net 4.0
2. Server was recently patched with the latest .Net patches (as at 2018/11/08)
3. The database in question was mirrored

1. 运行 .Net 4.0 的遗留应用程序
2. 服务器最近打了最新的 .Net 补丁（截至 2018/11/08）
3. 有问题的数据库被镜像

There were no deadlocks and the client services as well as SQL Server were well-resourced.

没有死锁，客户端服务以及 SQL Server 资源充足。

Our solution:

我们的解决方案：

1) We tested a Roll-back of the patches/service pack and it worked (Not recommended)

1）我们测试了补丁/服务包的回滚并且它有效（不推荐）

2) (Recommended): According to Microsoft, the conditions above set us up for the error:

2）（推荐）：根据微软的说法，上述条件为我们设置了错误：

When an application uses the Microsoft .NET Framework 3.5 or Microsoft .NET Framework 4 data provider for Microsoft SQL Server (SQLClient) to connect to a mirrored database, you may receive the following error message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) Note If the ConnectionTimeout property is set to the default value of 15 seconds, you may receive the error message after the connection is open for 1.2 seconds.

当应用程序使用 Microsoft SQL Server (SQLClient) 的 Microsoft .NET Framework 3.5 或 Microsoft .NET Framework 4 数据提供程序连接到镜像数据库时，您可能会收到以下错误消息：

超时已过。操作完成前超时时间已过或服务器未响应。在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException 异常，Boolean breakConnection) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 在 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 错误) 在 System.Data .SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trust&SystemServerCert,) SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout,

Cause

原因

This issue occurs because of an error in the connection-retry algorithm for mirrored databases.

When the retry-algorithm is used, the data provider waits for the first read (SniReadSync) call to finish. The call is sent to the back-end computer that is running SQL Server, and the waiting time is calculated by multiplying the connection time-out value by 0.08. However, the data provider incorrectly sets a connection to a doomed state if a response is slow and if the first SniReadSync call is not completed before the waiting time expires.

Note The slow response in this case may be triggered either by the server or by network latency.

出现此问题的原因是镜像数据库的连接重试算法出错。

使用重试算法时，数据提供程序等待第一次读取 (SniReadSync) 调用完成。调用被发送到运行 SQL Server 的后端计算机，等待时间的计算方法是将连接超时值乘以 0.08。但是，如果响应很慢并且第一个 SniReadSync 调用在等待时间到期之前未完成，则数据提供程序错误地将连接设置为注定状态。

注意 这种情况下的慢响应可能由服务器或网络延迟触发。

Resolution

解析度

This issue is resolved in the .NET Framework 4.5.2. In addition to the .NET Framework 4.5.2, there are hotfixes available for other .NETFramework versions

此问题已在 .NET Framework 4.5.2 中解决。除了 .NET Framework 4.5.2 之外，还有可用于其他 .NETF ramework 版本的修补程序

---

---

More details and Hotfix download: https://support.microsoft.com/en-us/help/2605597/fix-time-out-error-when-a-mirrored-database-connection-is-created-by-t

更多详情和修补程序下载：https: //support.microsoft.com/en-us/help/2605597/fix-time-out-error-when-a-mirrored-database-connection-is-created-by-t

回答by user3173814

just stop and start sql server browser service again, it solved my problem

只需停止并再次启动 sql server 浏览器服务，它就解决了我的问题

回答by Harvey

I found this problem too but I managed to fix it.

我也发现了这个问题，但我设法解决了它。

Originally I had set all SQL Server services to be started manually by me. But this (after a while of starting just SQL Server (server_name), this error came up. To rememdy it, I found that SQL Server Browser and SQL Server Agent (server_name) needed to be started too.

最初我已经将所有 SQL Server 服务设置为由我手动启动。但是这个（只启动SQL Server（server_name）一段时间后，出现了这个错误。为了补救它，我发现SQL Server Browser和SQL Server Agent（server_name）也需要启动。

SQL Server Agent (server_name) would not be started manually as it was not being used, so I set it back to automatic so it will start when another process uses it.

SQL Server 代理 (server_name) 不会被手动启动，因为它没有被使用，所以我将它重新设置为自动，以便在另一个进程使用它时启动。