none
Time out expired issue in production RRS feed

  • Question

  • Hi .

    We are using a C# console application which gernerates a excel file.

    The query is pretty straight forward select query but it is giving timeout.

    Can we increase the commandtimeout property to 1 min or 3 mins? how much we can increase?

    if we give command.CommandTimeout = 90, will this mean 90 seconds?

    what is the maximum we can give. Will it affect the other application performance, we have another web application pointing to the same database.

    PFb the error details from log.

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. Stack Trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAct

    Thanks,

    Wednesday, September 7, 2016 2:31 PM

Answers

  • “Can you please tell me if the time out is set to more than 90 sec, will it be a load to database ,

    The command timeout expiration event causes SQL execution to stop immediately. So if you set it to 90 and the query isn’t done – the query will cancel. So yes, a longer running query on your database means you are consuming resources.

    “this timeout is handled by ADO.NET right? does it mean that the query will be running database even after the timeout.?

    Yes its part of ADO.NET. The query will stop if the time out is hit.

    I think the main drawback is that you may have a performance problem with the SQL you are running. And perhaps increasing the timeout, you are just masking the underlying problem.

    You should always try to optimize your SQL first.


    william xifaras

    Thursday, September 8, 2016 5:47 PM

All replies

  • Just to be clear the default for the SqlCommand.CommandTimeout property is 30 seconds.

    "The time in seconds to wait for the command to execute. The default is 30 seconds."

    Setting it to 90, would put up to 90 seconds. Setting it to 120, would set it to 2 minutes etc etc. 

    0 means wait indefinitely and no timeout will be raised. When a timeout occurs, execution of the TSQL stops immediately and it is untrappable.

    In ado.net - in order to increase the timeout, you must increase the sqlcommand timeout and the sqlconnection timeout. The default for sqlconnection is 15 seconds.


    william xifaras

    Wednesday, September 7, 2016 6:52 PM
  • Thanks for the reply.

    Can you please tell me if the time out is set to more than 90 sec, will it be a load to database ,

    because we have a web application accessing the same database.

    Please tell the drawback of overriding this default time out.

    One more thing , this timeout is handled by ADO.NET right? does it mean that the query will be running database even after the timeout.?


    Thursday, September 8, 2016 1:26 PM
  • “Can you please tell me if the time out is set to more than 90 sec, will it be a load to database ,

    The command timeout expiration event causes SQL execution to stop immediately. So if you set it to 90 and the query isn’t done – the query will cancel. So yes, a longer running query on your database means you are consuming resources.

    “this timeout is handled by ADO.NET right? does it mean that the query will be running database even after the timeout.?

    Yes its part of ADO.NET. The query will stop if the time out is hit.

    I think the main drawback is that you may have a performance problem with the SQL you are running. And perhaps increasing the timeout, you are just masking the underlying problem.

    You should always try to optimize your SQL first.


    william xifaras

    Thursday, September 8, 2016 5:47 PM
  • I agree with William.

    This is a SQL query problem; either you have a query that does huge complicated aggregations over a very large dataset, or the query can be optimized.

    Maybe post the query here, or in the transact sql forum.

    regards,

    Nico


    Regards, Nico <br/>

    Friday, September 16, 2016 10:47 AM
  • When the non-zero CommandTimeout limit (in seconds) is reached, ADO.NET sends a cancel request to SQL Server to terminate the running query.  When SQL Server honors the cancel request, the batch is rolled back and terminated.  No further processing occurs on the server, including a CATCH block, if any.

    The default 30 second CommandTimeout is arbitrary but helps avoid bad queries and long-term blocking.  The appropriate value depends on the maximum time the query should run.  If your query is complex and requires scans of billions or rows to generate the Excel file, that could take quite a bit of time.  The performance impact on other concurrent activity during query execution will depend much on the performance capability of database server hardware and the demand of other activity.

    I suggest you first perform query and index tuning such that the query runs efficiently before increasing the timeout.  This will reduce the impact on other activity and make better use of available resources.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, September 16, 2016 11:29 AM