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

Question

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) +…

Accepted Answer

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/

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

Solution

Problem

Related problems