The timeout period elapsed prior to completion of the operation or the server is not responding.

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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

Posted 30-Apr-18 0:35am

Member 11381291

Updated 30-Apr-18 3:03am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-04-30T00:43:00

Solution 1

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.

Posted 30-Apr-18 0:43am

OriginalGriff

Comments

Member 11381291 30-Apr-18 6:46am

i have already SqlCommand.CommandTimeout set at 120 in code. still it gets error

OriginalGriff 30-Apr-18 7:02am

And how long does the query take to run in SSMS?

Member 11381291 30-Apr-18 7:06am

it Takes 14 to 15 Sec Maximum. But This error occurs sometimes.

Wendelius · Answer 2 · 2018-04-30T03:04:00

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