操作完成之前经过的超时时间或服务器没有响应。 [英] The timeout period elapsed prior to completion of the operation or the server is not responding.

问题描述

我在Server上托管了一个数据库。一个存储过程用于在一个表中插入或更新数据。在过程中使用Try Catch Block进行数据存储。对于存储数据使用'用户定义的表类型'，此类型也用作存储过程中的输入参数。依赖于数据行，数据将更新或插入。



以下Exception Occure有时用于该过程 -



异常 - System.Data.SqlClient.SqlException（0x80131904）：执行超时已过期。操作完成之前经过的超时时间或服务器没有响应。 ---> System.ComponentModel.Win32Exception（0x80004005）：等待操作超时

在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，布尔breakConnection，Action`1 wrapCloseInAction）
$ b System.Data.SqlClient.SqlInternalConnection.OnError上的$ b（SqlException异常，Boolean breakConnection，Action`1 wrapCloseInAction）

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj，Boolean callerHasConnectionLock，Boolean asyncClose）

在System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）

at System System.Data.SqlClient.SqlDataReader.get_MetaData（）
System.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader） ds，RunBehavior runBehavio r，String resetOptionsString，Boolean isInternal，Boolean forDescribeParameterEncryption）

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，Boolean async，Int32 timeout，Task& task，Boolean asyncWrite，Boolean inRetry，SqlDataReader ds，Boolean describeParameterEncryptionRequest）

在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String method，TaskCompletionSource`1 completion， Int32超时，任务和任务，布尔& usedCache，布尔asyncWrite，布尔inRetry）

在System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String method）

at System.Data.SqlClient.SqlCommand.ExecuteScalar（）

，位于e：\Builds \\中的Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar（IDbCommand命令） EntLib \Latest\Source \Blocks\Data\Src\Data\Database.cs：第476行

at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar（DbCommand命令） ）在e：\Builds \ EntLib \ Latest\Source中Blocks \Data\Src\Data\Database.cs：第998行

at PearlSolution.Data.Test_Param_ValueDAC.SaveParameterValue（DataTable dtTestResult，String approveLevelID，String stateIDs，String testregnID）

ClientConnectionId：86f7aed4-1a4c-4662-9d23-413ea828c0a0

错误号码：-2，状态：0，等级：11



我尝试过：



在商店中我更改了更新和插入查询。首先使用cursur存储或更新数据。

之后我改为



更新表1来自表2（用户定义表类型）

从table2插入table1（用户自定义表类型）



但我仍然有同样的错误

I have one Database hosted on Server.for one Store Procedure use to Insert or Update data in One Table.In Procedure use Try Catch Block for Data Store. for Store Data Used 'User Defined Table Type',also this type is used as input parameter in store procedure.depend on data row, data will update or insert.

following Exception Occure sometimes for that procedure -

Exception - System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(IDbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 476
at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command) in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 998
at PearlSolution.Data.Test_Param_ValueDAC.SaveParameterValue(DataTable dtTestResult, String approveLevelID, String stateIDs, String testregnID)
ClientConnectionId:86f7aed4-1a4c-4662-9d23-413ea828c0a0
Error Number:-2,State:0,Class:11

What I have tried:

In store Procedure i change Update and Insert Query. first use cursur to store or update data.
after that i change to

update table1 from table 2(User Defined Table Type)
Insert Into table1 from table2(User Defined Table Type)

but still i have this same error

推荐答案

简单地说，假设SQL服务器挂起，你的UPDATE操作花费的时间太长而且SqlCommand对象超时。



您可以尝试更改 SqlCommand.CommandTimeout属性（System.Data.SqlClient） [ ^ ]比默认的30秒还要早，但它取决于你（以及你的桌子的大小）工作设置它的价格。

尝试计时SSMS中的命令，应该让你知道你应该需要什么级别的超时。

Simply put, your UPDATE operation is taking too long and the SqlCommand object times out, assuming that the SQL server is "hung".

You could try changing the SqlCommand.CommandTimeout Property (System.Data.SqlClient)[^] to something otehr than the default 30 seconds, but it#s up to you (and teh size of your table(s)) to work out how much to set it for.
Try timing the command in SSMS and that should give you an idea what level of timeout you should need.

如果语句的执行时间变化很大，听起来像是一个系统范围的性能问题。



一些建议

- 使用性能监视器等Windows工具查看服务器的工作负载。监视例如CPU负载，可用内存量，分页，磁盘和网络吞吐量

- 使用SQL分析器发现导致不必要的系统负载的性能不佳的语句

- 确保你有适当的索引，特别是所有关键的陈述

- 如果需要考虑使用资源调控器

If the execution time for the statement varies a lot, it sounds like a system-wide performance problem.

Some advice
- Use Windows tools like performance monitor to view the workload of the server. Monitor for example CPU load, amount of available memory, paging, disk and network throughput
- Use SQL profiler to spot badly performing statement that cause unnecessary system load
- Ensure that you have proper indexing in place, especially for all critical statements
- If necessary consider using resource governor

这篇关于操作完成之前经过的超时时间或服务器没有响应。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！