SQL Server Timeout after 10 Seconds

Question

We are using OleDbConnection in a C# legacy application to connecto to a SQL Server 2000 where

• the connect string contains "Connect Timeout=5;" and
• we have the CommandTimeout set to 30 seconds.

Yet when we run a command (a few inserts in a transaction) when the server is slow the query times out after only 10 seconds.

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionPreYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction)
   at System.Data.SqlClient.SqlInternalTransaction.Commit()
   at System.Data.SqlClient.SqlTransaction.Commit()
   [...]

What am I missing? Is there a timeout value that I have not set that defaults to 10 seconds?

Answer 1

I think it is not too likely, because usually yields a different exception, but maybe you're running into the default transaction timeout, which is incidentally 10 seconds.

Add the following to your "app.config", and adjust the timeout to a larger value:

<system.transactions>
     <defaultSettings timeout="00:10:00"/>  
</system.transactions>

Note that if that actually solves your problem, you might want to specify the actual timeout inside your code, for just the transaction in question (e.g. by using the respective options for the TransactionScope class).