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

10

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/

sql
sql-server
tsql
sql-server-2012
asked on Stack Overflow Nov 19, 2013 by user960567 • edited Nov 19, 2013 by user960567

6 Answers

13

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/

answered on Stack Overflow Nov 19, 2013 by user960567 • edited Aug 4, 2016 by RMalke
3

Try to execute this command:

exec sp_updatestats
answered on Stack Overflow Nov 19, 2013 by Dhaval
1

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:

answered on Stack Overflow Jun 15, 2015 by Jon Schneider • edited May 23, 2017 by Community
0

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

answered on Stack Overflow Jan 16, 2014 by user3173814
0

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 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.

answered on Stack Overflow Jan 14, 2015 by Harvey
0

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

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

Our solution:

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

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

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.

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.

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 .NET Framework versions


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

answered on Stack Overflow Nov 10, 2018 by user919426

User contributions licensed under CC BY-SA 3.0