Connection timeout for SQL server

Question

Can I increase the timeout by modifying the connection string in the web.config?

Answer 1

Yes, you could append ;Connection Timeout=30 to your connection string and specify the value you wish.

The timeout value set in the Connection Timeout property is a time expressed in seconds. If this property isn't set, the timeout value for the connection is the default value (15 seconds).

Moreover, setting the timeout value to 0, you are specifying that your attempt to connect waits an infinite time. As described in the documentation, this is something that you shouldn't set in your connection string:

A value of 0 indicates no limit, and should be avoided in a ConnectionString because an attempt to connect waits indefinitely.

Answer 2

Hmmm...

As Darin said, you can specify a higher connection timeout value, but I doubt that's really the issue.

When you get connection timeouts, it's typically a problem with one of the following:

1. Network configuration - slow connection between your web server/dev box and the SQL server. Increasing the timeout may correct this, but it'd be wise to investigate the underlying problem.

2. Connection string. I've seen issues where an incorrect username/password will, for some reason, give a timeout error instead of a real error indicating "access denied." This shouldn't happen, but such is life.

3. Connection String 2: If you're specifying the name of the server incorrectly, or incompletely (for instance, mysqlserver instead of mysqlserver.webdomain.com), you'll get a timeout. Can you ping the server using the server name exactly as specified in the connection string from the command line?

4. Connection string 3 : If the server name is in your DNS (or hosts file), but the pointing to an incorrect or inaccessible IP, you'll get a timeout rather than a machine-not-found-ish error.

5. The query you're calling is timing out. It can look like the connection to the server is the problem, but, depending on how your app is structured, you could be making it all the way to the stage where your query is executing before the timeout occurs.

6. Connection leaks. How many processes are running? How many open connections? I'm not sure if raw ADO.NET performs connection pooling, automatically closes connections when necessary ala Enterprise Library, or where all that is configured. This is probably a red herring. When working with WCF and web services, though, I've had issues with unclosed connections causing timeouts and other unpredictable behavior.

Things to try:

1. Do you get a timeout when connecting to the server with SQL Management Studio? If so, network config is likely the problem. If you do not see a problem when connecting with Management Studio, the problem will be in your app, not with the server.

2. Run SQL Profiler, and see what's actually going across the wire. You should be able to tell if you're really connecting, or if a query is the problem.

3. Run your query in Management Studio, and see how long it takes.

Good luck!

Answer 3

If you want to dynamically change it, I prefer using SqlConnectionStringBuilder .

It allows you to convert ConnectionString i.e. a string into class Object, All the connection string properties will become its Member.

In this case the real advantage would be that you don't have to worry about If the ConnectionTimeout string part is already exists in the connection string or not?

Also as it creates an Object and its always good to assign value in object rather than manipulating string.

Here is the code sample:

var sscsb = new SqlConnectionStringBuilder(_dbFactory.Database.ConnectionString);

sscsb.ConnectTimeout = 30;

var conn = new SqlConnection(sscsb.ConnectionString);