Currently, All my SQL Request showing "System.ComponentModel.Win32Exception: The wait operation timed out"
Solution 1
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
DBCC INPUTBUFFER (58)
This will give you the actual SQL. If you want,you can kill this transaction,
KILL 58
BTW, In my application I can use READ COMMITTED data,
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Or
Select * from Products WITH NoLock
Here is another way to find the SQl quickly,
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/
Solution 2
Try to execute this command:
exec sp_updatestats
Solution 3
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).
Related:
Imran Qadir Baksh - Baloch
Updated on July 05, 2022Comments
-
Imran Qadir Baksh - Baloch about 2 years
Suddenly, all of sql server requests showing "System.ComponentModel.Win32Exception: The wait operation timed out". What is the quickest way to find the issue?
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,
http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/