ADO.net connection timedout before the connection timeout value

Question

We have a WCF Data Service connecting to a SQL Server 2008 R2 database. When the service is trying to access data from the database the connection is getting timed out before the Connection Timeout value and the timeout exception is thrown.

This is the connection string:

Data Source=XXXX;Initial Catalog=XXXX;MultipleActiveResultSets=False;
Connection Timeout=80;Integrated Security=false;User ID=XXX;Password=XXX

In this connection string, even though we gave 80sec as the Connection Timeout, the connection gets timed out at around 700ms and throws below exception

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding.

This is happening on some queries only.

Any help is appreciated.

Does it time out upon opening the connection or upon executing a command over the connection? There are two types of timeouts: Connection Timeout and Command Timeout. The latter is set on the command. — PHeiberg, Nov 15 '12 at 09:30

score 3 · Answer 1 · edited May 23 '17 at 12:27

3

It's not the ConnectionTimeout that causes the error; it's the CommandTimeout. See also this question.

Therefore, the solution is to set a higher value for CommandTimeout in case of more expensive queries.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 15 '12 at 09:31

Cristian Lupascu

39,078
16
100
137

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

This type of timeout can have three causes;

There's a deadlock somewhere
The database's statistics and/or query plan cache are incorrect
The query is too complex and needs to be tuned

Taken from the following links

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

try to optimise the query as mentioned here

edited May 23 '17 at 12:18

Community

1
1

answered Nov 15 '12 at 09:28

Thangamani Palanisamy

5,152
4
32
39

Yes I have seen right now. As per Sivvala only some query will cause this error. So if we set SqlCommand.CommantTimeout=0, Hope the problem will be solved – Thangamani Palanisamy Nov 15 '12 at 09:32
Yes, but I think it's not advisable to set it to 0; I usually prefer to set a limit on the execution time. – Cristian Lupascu Nov 15 '12 at 09:36
Yes I agree he should try to optimize the long running query. – Thangamani Palanisamy Nov 15 '12 at 09:46
Setting it to 0 is just to diagnose the issue. You need to optimize the query or at least set the timeout to some sensible amount of time that you are comfortable making your users wait. – PHeiberg Nov 15 '12 at 09:46
Here the command gets timed out in around 700ms but the default value for commandtimeout is 30sec as per msdn documentation. – sathwick sivvala Nov 16 '12 at 09:01
Will there be any reason to timeout before the default value? – sathwick sivvala Nov 16 '12 at 10:05
\no its depends upon the query . instead of adjusting timeout value. please try to optimize the slow running queries or else can you post the query that gets timed-out, we will do it for you – Thangamani Palanisamy Nov 18 '12 at 09:24

score 0 · Answer 3 · answered Dec 09 '13 at 10:20

I had the same problem and google brought me here.

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

In my case, the mssql firewall port (default = 1433 TCP) was not yet open on the db server.

ADO.net connection timedout before the connection timeout value

3 Answers3