Connection Timeout

Hi, I have a client application that submits heavy processing query to an instance of Sql Server 2005 (Enteriprise Edition). After a few seconds the following exception is raised: "The time available has expired before the completion of the operation or the server is not responding". If I submit to the same application light processing queries the exception is not raised.
The client application is written in VB.net and uses ADO.net. (SqlConnection, SqlCommand,.. classes). The "Timeout connection" property is set to 1000.
Any suggestion?
Thank you.

Hi Ivan,

The ConnectionTimeout property of SqlConnection defines the time boundaries to open a physical connection to the SQL Server. In your case, the physical connection seems to be established within a reasonable frame. However, after the connection is up, the client and the server begin exchanging security information as part of the login process. This is related to a different "login timeout", which unfortunately you don't seem to be able to control from SqlClient.

What is happening on the server during the login process is related to the SOS scheduler operations - I won't go into deep detail, but in a nutshell all the tasks (including processing of login information) are queued. If the queue length is very long, the login information processing may be delayed beyond the login timeout and you will experience your symptoms above. There could also be other factors - the login processing makes calls to the security subsystem, if the entire SQL machine is under heavy stress, those may take longer time, too. You should approach this as a perf problem and use appropriate tools like perfmon, the SOS DMVs, etc.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Connection Timeout is the timeout for connecting to SQL Server. What you need is COMMAND timeout. Set COMMAND timeout=0 for indefinite period.

Command timeout has to be set on the command object - because it is related to a particular SQL processing.