none
SqlException - Timeout when not nearly enough time has elapsed RRS feed

  • Question

  • In a long-running application with many long-running queries (up to several minutes each), I'm seeing occasional SqlExceptions with the message "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding".  In all cases, these are occurring on a SqlCommand that has had it's timeout set to 3600 seconds (one hour), but the exception is happening anywhere from 0 to 30 seconds into execution of the command - nowhere near an hour. 

    The second part of that message "...or the server is not responding" suggests that there could be other causes for this exception other than a true timeout, but I don't see any indication of anything else interesting happening at the same time.  The Windows Application and System event logs don't show anything at that time.

    Any ideas?

    Win 2008R2 x64, SQL 2008 SP1 x64, client is running on the server machine.  There's only a single client running and it never has more than a single connection open at a time.


    -cd [VC++ MVP] Mark the best replies as answers!
    Thursday, June 17, 2010 2:52 PM

All replies

  • Hello Carl,

     

    Welcome to ADO.NET Managed Providers!

     

    Besides the command timeout, sometimes the connection timeout may cause this issue as well.  Please verify whether you have set the connection timeout in the connection string like:

    =========================================================================

    “Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timout=300”

    =========================================================================

    Additional references:

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

     

    If it does not solve the issue, could you please provide us with more detailed information about the error and connection string that you are using?  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 21, 2010 2:59 AM
    Moderator
  • I can see from the stack trace that this is not a connection timeout.  I believe that the error message is different in that case as well.  The stack trace from the exception is:

    SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
      at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
      at ... my code

    The connection string is nothing special - localhost, default instance, integrated security, initial catalog.  I have no reliable repro for this error - I've seen it only a couple of times out of millions of rows worth of transactions.

     

     


    -cd [VC++ MVP] Mark the best replies as answers!
    Monday, June 21, 2010 6:14 AM
  • Hi Carl,

     

    Thank you for your following up!

     

    Does the timeout exception still exist? 

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, June 23, 2010 6:23 AM
    Moderator
  • It remains that I've seen it a couple of times but I do not have a reliable (or even unreliable) repro scenario.

     


    -cd [VC++ MVP] Mark the best replies as answers!
    Thursday, June 24, 2010 4:07 PM
  • Hi Carl,

     

    Are there any obvious problems the timeout exceptions lead to?   It's really hard to figure out where is the root cause of the problem without detailed information.  :-)

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 28, 2010 9:15 AM
    Moderator
  • The obvious problem that the timeout exception leads to is that the application stops working when it shouldn't have.

    As to the cause, I haven't seen the error again recently, so I still have no idea what the exact circumstances were that caused the error in the first place.

     


    -cd [VC++ MVP] Mark the best replies as answers!
    Friday, July 16, 2010 7:54 PM