CODEWITHSUNDEEP
c#asp.netiis-7database-connectionconnection-pooling
Tom Gullen
Tom Gullen

Reputation: 61737

ASP.net Timeouts due to connections not being closed

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This is the first ASP.net site I developed a long time ago, it has this code at the top of a lot of pages (and in a lot of methods).

cn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
cn.Open();

A lot of pages don't have:

cn.Close();

Also none of the pages do using(SqlConnection...), (although all the data readers are in using blocks).

First question is, is the the primary candiate for the error?

Second question is, is the best way to solve this refactoring/manually searching for unclosed connections? I know it's an ugly hack, but the time spent refactoring will be huge, but can we set a scheduled task to recycle the connection pool once a day at 3am or something?

Upvotes: 1

Views: 1283

Answers (2)

Hasan Fahim
Hasan Fahim

Reputation: 3885

Even I have encountered this error in the application that I once worked on. The problem that I identified was the same - no using statements and no close calls.

I would advise you to search the whole project for SqlConnection and then include the SqlConnection, SqlCommand and SqlDataAdapter in using statements and also do a connection.close within the sqlconnection using statement. Together with this in the config file increase the timeout of the connection within the connection string. You can also you CommandTimeout property of SqlCommand.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062770

Yes, that is the primary cause of the error. Currently, many of those connections will wait until the next GC to re-pool the underlying connection. You will exhaust the pool (and database connections) pretty quickly.

The best way of refactoring this is to add the missing using, such that the connection is scoped. Personally I'd also refactor that to a single method, i.e.

using(var cn = SomeUtilityClass.GetOpenConnection())
{...}

Closing the reader does little unless the reader is marked to close the connection; and having the data-reader close the connection (via a behaviour) sort of works, but it assumes you get as far as consuming the reader - it won't necessarily behave well in error scenarios.

Upvotes: 1

Related Questions