2

First I unplug my network cable.

Then I try to connect to SQL Server from within C# using an SqlClient.SqlConnection object and a connection string that either does not specify a connection timeout (and thus defaults to 15 seconds): Integrated Security=SSPI;Persist Security Info=False;Data Source=MYSERVER;Database=MYDB"

or a connection string that does specify a timeout, even shorter at 5 seconds: "Integrated Security=SSPI;Persist Security Info=False;Data Source=MYSERVER;Database=MYDB;Connect Timeout=5"

Either way, the call to connection.Open returns after 42 seconds with my timeout error. If I plug my network cable back in earlier, I can provoke an error sooner. Why is the connection timeout having no effect? Is this documented somewhere? Is there a way around it?

user12861
  • 2,358
  • 4
  • 23
  • 41
  • My guess is that it is looking for a SQL timeout based on a found SQL box (a pure SQL timeout). A network timeout is separate. You could ping before you try and connect. – paparazzo Jun 20 '12 at 15:21

1 Answers1

2

Connect Timeout applies ONLY when it is able to resolve the server and waits for a connection.

As per MSDN also, Connect Timeout is the length of time (in seconds - default 15) to wait for a connection to the server before terminating the attempt and generating an error.

Note:
5 seconds is too less for networking hardwares to detect the network is available or not. It should not be less than 10 seconds.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92
  • Thanks for the info. Is there any way to time out in less than the 42 seconds I'm seeing if there's no network connection? – user12861 Jun 20 '12 at 15:32
  • You can ping the ip of sql server machine before attempting connection. Refer http://www.makhaly.net/Blog/11 – Romil Kumar Jain Jun 20 '12 at 17:51
  • I suppose that would work, but I don't really want to ping every time, and even if I did there's still a race condition. If there were some way to set a true timeout, that would be nice. Maybe they just don't have that functionality for an SqlConnection though. – user12861 Jun 20 '12 at 18:08
  • @user12861, I think you must post a new question regarding your new queries. – Romil Kumar Jain Jun 21 '12 at 06:55
  • http://stackoverflow.com/questions/3619347/how-to-make-sqlconnection-timeout-more-quickly seems to indicate there's no really good solution, just workarounds susceptible to race conditions. – user12861 Jul 03 '12 at 16:00