Timeout expired

Question

Timeout expired

Valued Member

Points: 58

May 17, 2006 at 3:34 pm

I keep getting an error messages from C# .NET web application when i am going from one page to another one. Here is the error message:

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45   System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38   GMI.DataAccessLayer.SQLCommandAndDataSet.ExecuteAndFill(Boolean a_bPreserveCommandObject) +136   GMI.DataAccessLayer.DataAccessLayer.CompanyUserDataSet(String sSessionValue, String username, Guid mChanged_gUserSessionID) +72   GMI.Intranet.DataEntry.Home.CreateRecentlyUsedCompaniesDataSource(String sSessionValue, String username, Guid mChanged_gUserSessionID) +37   GMI.Intranet.DataEntry.Home.Page_Load(Object sender, EventArgs e) +1449   System.Web.UI.Control.OnLoad(EventArgs e) +67   System.Web.UI.Control.LoadRecursive() +35   System.Web.UI.Page.ProcessRequestMain() +731

According to my abservations It happends when the SQL server pritty busy or when i am running a complicated query from SQL analyzer. Is there a way to improve perfomance of the SQL server?

Thank you in advence

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Many ways, but it depends greatly on what the root problem is.

Have a look in the performance forum, iirc there are a few posts on getting started with monitoring and tuning.

It could be hardware bottleneck, poor indexes, excessive blocking, inefficient queries

Another thing you can do is increase the timeout setting of the SQL command in the C# app.

SQL Server itself has no concept of timeout, it's purely a client-side error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grigory Libo Valued Member Points: 58 More actions · Answer 2

I have tryied to increase the the timeout settings in the config file and it does not help. It is interesting when we are not running any complex batches on the server site it works almost always good. Once i am running a complex batch it starts giving me this type of errors. Also tell me please what do you mean by saying hardware bottleneck. Where should i go and check if my indexing is good. My query is good because the they no taking a long time to run about 2 or 5 seconds. Also tell me please where should i chek about blocking. As far as i know in my code there is no limitation in blocking so the query will be executed with out any blocking.

Thank you very much in advence.

Greg

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Not the timeout in the config. Is something like cmd.Timeout in the code, after you create the command, but before you execute it. Not a C# expert.

For blocking, run sp_who2 and check to see if any have a entry in the blocked column.

For indexes, is very complex. Start by finding the slow running queries.

SQL Profiler can help with that. Once you've found the sow running onces, you can run them in query analyser with the execution plan option on.

Look for table scans and index scans.

You can also run the database tuning adviser and see what it suggests. It's a good place to start though it's not perfect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grigory Libo Valued Member Points: 58 More actions · Answer 4

In my code i have time out setting:

m_cds.c.CommandTimeout = 216000;

Do you know what is the maximum number i can put there?

Thanks a lot.

Greg

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

No idea. The .net docs should tell you.

That's already a pretty high value. how long does that query take in QA?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grigory Libo Valued Member Points: 58 More actions · Answer 6

In QA it takes between 35 and 50 seconds to run and another query about 5 sec to run

Thanks

Greg

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Weird. That's a little slow, but not close to causing a timeout. Even the default value is 5 min.

How long does it take from the web page?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grigory Libo Valued Member Points: 58 More actions · Answer 8

If it is not getting a time out error between 35-45 sec. If it is getting an error it takes 45-50 sec until the error message appears.

Greg

Grigory Libo Valued Member Points: 58 More actions · Answer 9

Thnak you very much it was very helpfull. I fixed it by improving my query. I cleaned up table therfore speed up the query it self.

THANK YOU

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

My pleasure. I'm glad it's fixed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass