突然间,所有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,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,Boolean returnStream,Boolean async,Int32 timeout,Task& task,Boolean asyncWrite,sqlDataReader ds) +1406 System.Data.sqlClient.sqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,String method,TaskCompletionSource`1 completion,Boolean asyncWrite) +177 System.Data.sqlClient.sqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,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,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
我得到了导致阻塞问题的sql,
http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/
解决方法
在这里我怎么能找到这个问题,
首先检查所有打开的事务数据库,
首先检查所有打开的事务数据库,
DBCC OPENTRAN ('Databse')
如果有一个打开的事务,那么抓住它的SPID并将其放在INPUTBUFFER中
DBCC INPUTBUFFER (58)
这将给你实际的sql.如果你想,你可以杀死这个交易,
KILL 58
BTW,在我的应用程序中,我可以使用READ COMMITTED数据,
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
要么
Select * from Products WITH NoLock
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/
