89

I have the following connection string(get from a property of sql server):

Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\myUser\Desktop\adoBanche\Banche\bin\Debug\banche.mdf;Integrated Security=True;Connect Timeout=30

I don't understand what mean Timeout=30. Someone could explain what means?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Tinwor
  • 7,765
  • 6
  • 35
  • 56

8 Answers8

125

That is the timeout to create the connection, NOT a timeout for commands executed over that connection.

See for instance http://www.connectionstrings.com/all-sql-server-connection-string-keywords/ (note that the property is "Connect Timeout" (or "Connection Timeout"), not just "Timeout")

From the comments:

It is not possible to set the command timeout through the connection string. However, the SqlCommand has a CommandTimeout property (derived from DbCommand) where you can set a timeout (in seconds) per command.

Do note that when you loop over query results with Read(), the timeout is reset on every read. The timeout is for each network request, not for the total connection.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
  • 20
    From the link I can infer that **it is NOT possible to set command timeout from connection string**. We can only set the connection timeout instead. – RBT Aug 20 '16 at 07:06
  • 5
    It is however possible to set a command timeout using `SqlCommand.CommandTimeout` property (in seconds). https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx – P.Brian.Mackey Apr 13 '18 at 20:37
  • 1
    or by setting objectContext.CommandTimeout property if you are using EF – Mujib Khan Sep 30 '22 at 11:06
  • SqlConnectionStringBuilder provides a CommandTimeout property for adding command timeout to your connection string. – Will May 08 '23 at 18:09
  • @Will are you very sure it is not a [ConnectTimeout](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.connecttimeout?view=dotnet-plat-ext-7.0#system-data-sqlclient-sqlconnectionstringbuilder-connecttimeout)? – Hans Kesting May 08 '23 at 18:56
  • @HansKesting The following link shows the documentation for the CommandTimeout property of SqlConnectionStringBuilder that I was referring to. The default for CommandTimeout is apparently 30seconds. https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnectionstringbuilder.commandtimeout?view=sqlclient-dotnet-standard-5.1 – Will May 16 '23 at 15:42
23

Connection Timeout=30 means that the database server has 30 seconds to establish a connection.

Connection Timeout specifies the time limit (in seconds), within which the connection to the specified server must be made, otherwise an exception is thrown i.e. It specifies how long you will allow your program to be held up while it establishes a database connection. 

DataSource=server;
InitialCatalog=database;
UserId=username;
Password=password;
Connection Timeout=30

SqlConnection.ConnectionTimeout. specifies how many seconds the SQL Server service has to respond to a connection attempt. This is always set as part of the connection string.

Notes:

  • The value is expressed in seconds, not milliseconds.

  • The default value is 30 seconds.

  • A value of 0 means to wait indefinitely and never time out.

In addition, SqlCommand.CommandTimeout specifies the timeout value of a specific query running on SQL Server, however this is set via the SqlConnection object/setting (depending on your programming language), and not in the connection string i.e. It specifies how long you will allow your program to be held up while the command is run.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
Rae Lee
  • 1,321
  • 10
  • 11
  • 7
    I know this is a few years old, but the default is 15 seconds, as per the microsof docs https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout – Stephen Jun 04 '18 at 12:16
17

Connect Timeout=30 means, within 30second sql server should establish the connection.other wise current connection request will be cancelled.It is used to avoid connection attempt to waits indefinitely.

david.barkhuizen
  • 5,239
  • 4
  • 36
  • 38
sreejithsdev
  • 1,202
  • 12
  • 26
4

How a connection works in a nutshell

A connection between a program and a database server relies on a handshake.

What this means is that when a connection is opened then the thread establishing the connection will send network packets to the database server. This thread will then pause until either network packets about this connection are received from the database server or when the connection timeout expires.

The connection timeout

The connection timeout is measured in seconds from the point the connection is opened.

When the timeout expires then the thread will continue, but it will do so having reported a connection failure.

  • If there is no value specified for connection timeout in the connection string then the default value is 30.

  • A value greater than zero means how many seconds before it gives up e.g. a value of 10 means to wait 10 seconds.

  • A value of 0 means to never give up waiting for the connection

Note: A value of 0 is not advised since it is possible for either the connection request packets or the server response packets to get lost. Will you seriously be prepared to wait even a day for a response that may never come?

What should I set my Connection Timeout value to?

This setting should depend on the speed of your network and how long you are prepared to allow a thread to wait for a response.

As an example, on a task that repeats hourly during the day, I know my network has always responded within one second so I set the connection timeout to a value of 2 just to be safe. I will then try again three times before giving up and either raising a support ticket or escalating a similar existing support ticket.

Test your own network speed and consider what to do when a connection fails as a one off, and also when it fails repeatedly and sporadically.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
3

Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout%28v=vs.110%29.aspx

Christos
  • 53,228
  • 8
  • 76
  • 108
1

Maximum time between connection request and a timeout error. When the client tries to make a connection, if the timeout wait limit is reached, it will stop trying and raise an error.

Pablo Francisco Pérez Hidalgo
  • 27,044
  • 8
  • 36
  • 62
1

Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error. (MSDN, SqlConnection.ConnectionTimeout Property, 2013)

Kiril Rusev
  • 745
  • 3
  • 9
-5

By default connection timeout is 240 but if you are faceing the problem of connection time out then you can increase upto "300" "Connection Timeout=300"

Aditi_Systematix
  • 105
  • 2
  • 5