"Timeout expired" in ADO.NET

  • Thread starter Thread starter Nils Magnus Englund
  • Start date Start date
N

Nils Magnus Englund

Hi,

I've sent a similar post earlier - but after no more luck and a lot more
frustration, I am reposting the issue with some additional information.

It's regarding several web applications on the same server, connecting to a
SQL Server on another server.

I keep getting the following exception when accessing the database:

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

I am currently using the following code to connect, but I've had the same
problem with all the different code snippets I've tried, so I doubt the
problem lies herein:

-----
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
.... data code ....
connection.Close();
}
-----

The problem happens 1-5 times, after I refresh the page enough times, it
usually goes through. I've seen a number of potential solutions to the
problem - but none of them fits the problem.

I usually get the exception after not using the system for a while, but I
haven't experienced when the system is "in use", i.e. when the previous
request was no more than a couple of minutes ago, and no recompile of the
application has been done.

I _don't_ get enough connections to exceed the maximum - far from it (when I
don't get the exception, the connection pooling seems to work fine).

The exception is thrown instantly, not after the default 30 second timeout.

And after I had accidentally misconfigured the authentication in the web
application, the user didn't have access - I got "Login failed for user
(null)" - what's odd was that I _still_ got the "Timeout expired" exception
4-5 times before I finally got the expected "Login failed" exception. The
"Timeout expired" exception gets thrown even before I successfully connect
to the database!

Suggestions are very welcome, I've been struggling with this problem off and
on for several weeks now. It's only a big nuisance during development, but
naturally, it's a real showstopper for going into production with the
application(s).


Regards,
Nils Magnus Englund
 
1. Make sure the connection timeout is large enough - this is not normally
the problem,however.

2. Make sure the command timeout is large enough for the work you are doing
- this is a common problem.

NOTE: I find it best to always explicitly set timeout values.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Hi Gregory,

Thank you for your reply, but perhaps you didn't read the whole post? As I
mentioned, I get the exception instantly, no matter what the timeout values
are. Also, I get the exception before I would otherwise get the "Login
failed" exception.


Regards,
Nils Magnus Englund
 
Does the problem go away if you use a different web server?

Does the problem go away if you use a different database server?

It's possible it's some sort of network or machine configuration issue. I
would experiment with using different machines as the web server and
database server, and trying to have the web server and database server be
the same machine.
 
A little addendum;

the first exception has the following stack trace:

----------------------
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open() at
(...)
----------------------

Consecutive exceptions have the following stack trace:

----------------------
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open() at
(...)
----------------------


After I refresh the page a couple of more times (each time I get the
exception with the latter stack trace), I finally get the site without any
exceptions.


Regards,
Nils Magnus Englund
 
Back
Top