locked
Query timeout RRS feed

  • Question

  • If am not wrong, the default query timeout of a query submitted to database engine from a .net/java application is 30 seconds. This means that if query runs for more than 30 seconds the sql server throws exception to the application and terminates the query.

    However, I faced a strange issue today. The query timed out, i.e. it ran more than 30 seconds, the application log reported that it got the timeout exception but in SQL profiler said that the query ran for 90 seconds...

    Just to confirm that does the query timeout of 30 sec means that SQL server throws timeout exception to the calling application and terminates the query or it continues to execute the query?


    Cheers!!! SqlFrenzy

    Monday, May 20, 2013 9:08 PM

Answers

  • No, SQL Server does not throw any exception. SQL Server lets you run the query all night long if you wish. The query timeout is a client-side thing, and you can control it on the command object. What happens when the client gets tired of waiting, is that it sends SQL Server an Attention signal to tell it to stop executing. It is exactly the same mechanism as the red button in Management Studio.

    While SQL Server should stop executing, it must rollback the current statement, and if XACT_ABORT is ON, the entire transaction. That can take time. And if the spid is stuck in some external wait, it may remain stuck. That is why the duration can exceed 30 seconds.

    By the way, the default timeout of 30 seconds is a hole the head if you ask me. The default should be 0 - wait forever.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Monday, May 20, 2013 10:02 PM
    • Marked as answer by Ahmad Osama Wednesday, May 22, 2013 3:01 AM
    Monday, May 20, 2013 10:00 PM
  • thanks guys ... and correct me if am wrong that it is completely different from the remote query timeout setting which had a default value of 600 seconds

    Yes, that is also a client-side setting. More precisely, when SQL Server acts as a client to a remote data source.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ahmad Osama Wednesday, May 22, 2013 3:01 AM
    Tuesday, May 21, 2013 9:55 PM

All replies

  • No, SQL Server does not throw any exception. SQL Server lets you run the query all night long if you wish. The query timeout is a client-side thing, and you can control it on the command object. What happens when the client gets tired of waiting, is that it sends SQL Server an Attention signal to tell it to stop executing. It is exactly the same mechanism as the red button in Management Studio.

    While SQL Server should stop executing, it must rollback the current statement, and if XACT_ABORT is ON, the entire transaction. That can take time. And if the spid is stuck in some external wait, it may remain stuck. That is why the duration can exceed 30 seconds.

    By the way, the default timeout of 30 seconds is a hole the head if you ask me. The default should be 0 - wait forever.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Monday, May 20, 2013 10:02 PM
    • Marked as answer by Ahmad Osama Wednesday, May 22, 2013 3:01 AM
    Monday, May 20, 2013 10:00 PM
  • If am not wrong, the default query timeout of a query submitted to database engine from a .net/java application is 30 seconds. This means that if query runs for more than 30 seconds the sql server throws exception to the application and terminates the query.

    However, I faced a strange issue today. The query timed out, i.e. it ran more than 30 seconds, the application log reported that it got the timeout exception but in SQL profiler said that the query ran for 90 seconds...

    Just to confirm that does the query timeout of 30 sec means that SQL server throws timeout exception to the calling application and terminates the query or it continues to execute the query?


    Cheers!!! SqlFrenzy

    Hi,

    You should set connection timeout in connection string.

    Regards.


    Cuong

    Tuesday, May 21, 2013 4:40 AM
  • Just to be clear.

    SQL Server did not timeout.  SQL Server does not have a timeout on queries. 

    .NET/Java has a default timeout of 30 seconds, this can be configured in the application.

    Please see:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/09/06/why-is-my-command-not-timing-out-jdbc.aspx

      

    Tuesday, May 21, 2013 3:15 PM
  • thanks guys ... and correct me if am wrong that it is completely different from the remote query timeout setting which had a default value of 600 seconds

    Cheers!!! SqlFrenzy

    Tuesday, May 21, 2013 9:19 PM
  • Correct.  The "remote query timeout" is only for linked servers.

    Tuesday, May 21, 2013 9:26 PM
  • thanks guys ... and correct me if am wrong that it is completely different from the remote query timeout setting which had a default value of 600 seconds

    Yes, that is also a client-side setting. More precisely, when SQL Server acts as a client to a remote data source.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Ahmad Osama Wednesday, May 22, 2013 3:01 AM
    Tuesday, May 21, 2013 9:55 PM