none
SQL Server 2005 Timeout expired exception

    Question

  • Hi,

    I have a program in C# that I used to run on Visual Studio 2000 and SQL Server 2000 without any problem.

    Now I upgrade to VS2005 and SQL Server 2005 and get and exception while

    insert via SqlCommand.ExecuteNonQuery(). the insert is after many inserts to the same table by that program. the CommandTimeout is set to 600 (in the 2000 version I never had to change the default value of 30 seconds).

    the insert is through a connection that is kept open throughout the program for more than 30 minutes

    the exception message is:

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

    the trace is:

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    Thanks for your help !

    Monday, February 13, 2006 9:03 AM

Answers

  • Increasing the CommandTimeout property solves the problem for me...

    i.e.

    SqlCommand1.CommandTimeout = 500000

    will solve the problem...

    Thursday, March 25, 2010 2:29 PM

All replies

  • I having the same problem.  Sometimes if I re-execute the command it works but many times not.

    Thanks in advance for your help,
    Al

    Monday, February 13, 2006 4:18 PM
  • I forgot to mention that I am using the SATNDARD edition of SS 2005 and that the table has 931,806 rows before the job starts, the exception is after 26,332 inserts, when the table has 958,138 rows,

    and that the table has int identity primary key and a unique nonclustered index on 7 columns of types datetime, smallint & int.

    Tuesday, February 14, 2006 3:39 PM
  • I am stuck up with the same kind of problem. The operation that throws the exception is successful most of the times. The timeout happens once or twice in 10 attempts.

    From the SqlProfiler, it seems that the timeout happens when an insert statement is fired. The row being inserted has a column which has a large amount of data. Could this be a problem?

    Increasing the SqlCommand.CommandTimeOut doesnt help resolve the problem.

    The following link has some probable causes listed....i dont think the time out in my case is caused by any of these

    http://msdn2.microsoft.com/en-us/library/ms190181.aspx

    The error number is '-2' in my case.  Is it the same in your case?

     

     

     

    Tuesday, February 21, 2006 7:31 AM
  • from the stack provided in usha's mail, it seems that there is a connection timeout and not a command timeout. I did read in some link that you need to set both, which didnt seem very logical though. Perhaps setting a Connection Timeout = 100 or so would work in your case.
    Wednesday, February 22, 2006 8:56 AM
  • try with this code before your query

    Code Snippet


    Your Query
    go
    dump tran "You database" with no_log


    this skip the transaction log and your query is more fast.
    Monday, July 16, 2007 6:43 AM
  • Hi,

    I have a program in C# that I used to run on Visual Studio 2000 and SQL Server 2000 without any problem.

    Now I upgrade to VS2005 and SQL Server 2005 and get and exception while

    insert via SqlCommand.ExecuteNonQuery(). the insert is after many inserts to the same table by that program. the CommandTimeout is set to 600 (in the 2000 version I never had to change the default value of 30 seconds).

    the insert is through a connection that is kept open throughout the program for more than 30 minutes

    the exception message is:

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

    the trace is:

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    Thanks for your help !


    Sunday, August 23, 2009 4:32 PM
  • Increasing the CommandTimeout property solves the problem for me...

    i.e.

    SqlCommand1.CommandTimeout = 500000

    will solve the problem...

    Thursday, March 25, 2010 2:29 PM
  • extending a timeout to an issue that should be solved by performance monitoring and tweaking - is a BAD SOLUTION....c'mon - where are the technical people any more...

    lazy IT people just bandaid things...

    Friday, March 30, 2012 4:05 PM
  • http://support.microsoft.com/kb/915850/en-us      

     sp_fulltext_service 'verify_signature', 0;

    Thursday, April 18, 2013 8:33 PM
  • I hope you checked SQL server data and log files are out of antivirus scan purview sometimes connection is closed by Antivirus specially Symantec.

    You can contact your SOC and confirm from him


    Shashank Singh Attitude: Soldier..Sir we are surrounded from all sides by enemy.. Major: Good we can attack in any direction

    Monday, April 29, 2013 3:37 PM