ASP.NET SqlConnection Timeout issue

Question

Hello all, I have run into a frustrating issue which I originally thought was a connection leak but that does not seem to be the case. The secnario is this: the data access for this application is using the Enterprise Libraries (v4) from Microsoft. All data access calls are wrapped in using statements such as

using (DbCommand dbCommand = db.GetStoredProcCommand("sproc"))
{
     db.AddInParameter(dbCommand, "MaxReturn", DbType.Int32, MaxReturn);
     ...more code
}

Now the index of this application makes 8 calls to the database to load everything and I can bring the application to its knees by refreshing the index about 15 times. It seems that when the the database reaches 113 connections is when I recieve this error. Here is what makes this weird:

I have run similar code with the entlib on high traffic sites and have NEVER had this problem ever.

If I kill all the connections to the database and get the production application back up and running everytime I refresh the application I can run this SQL

SELECT DB_NAME(dbid) as 'Database Name', 
COUNT(dbid) as 'Total Connections' 
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

I can see the number of connections actively increasing with each page refresh. Running the same code on my local box with the same connection string does not cause this problem. Further if the production website is down I can fire up the site via Visual Studio and run it fine and the only difference between the two is that the production site has Windows authentication turned on and my local copy doesn't. Turning windows authentication off seems to have no effect on the server.

I have absolutely no clue what is causing this or why the connections are not being disposed of in SQL Server. The EntLib objects do no explose .Close() methods for anything so I can't explictily close the object.

Any thoughts? Thanks!

Edit

Wow I just noticed that I never actually posted the error message. Oy. The actual connection error is: 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.

Answer 1

Check that the stored procedure you are executing is not running into a row or table lock. Also if you can possibly try to deploy in another server and check if the application would crawl again.

Also try to increase the maximum allowed connections for your SQL server.

Answer 2

think the “Timeout Expired” error is a general issue and may have seveal causes. Increasing the TimeOut can solve some of them but not all.

You may also refer to the following links to troubleshoot and fix the error

http://techielion.blogspot.com/2007/01/error-timeout-expired-timeout-period.html

Answer 3

Could it be a configuration issue on the server?

How do you make a connection to the database on the production server?
That might be an area worth looking into.

Answer 4

While I don't know the answer I can suggest that for some reason connections are not being closed by you application when run in production. (Stating the obvious)

You might want examine your network configuration between the web server and sql server. High latency networks can cause connections not being closed in time.

Also it might help looking at the performance counters listed in the end of the following msdn article: http://msdn.microsoft.com/en-us/library/8xx3tyca%28VS.71%29.aspx

Finally, if nothing else helps, I'd get debugger and Enterprise Library source code on production and debug your code inside the enterprise library to find out why connections are not being closed.

Silly question are you properly closing your DataReader? If not this could be the problem and the difference in behaviour between dev and prod can be caused by different garbage collection patterns.

Answer 5

I would disable connection pooling and try to suppress it (heh). Just add ";Pooling=false" to your connection string.

Or, perhaps you could add something like the following 'cleanup' code to your page (which closes any connection left open when the page unloads) - right in the 'using' clause:

System.Web.UI.Page page = HttpContext.Current.Handler as System.Web.UI.Page;
if (page != null) {
           page.Unload += (EventHandler)delegate(object s, EventArgs e) {
                      try {
                                 dbCommand.Connection.Close();
                      } catch (Exception) {
                      } finally {
                                 result = null;
                      }
           };
}

Also, make sure you've enabled the 'shared memory' protocoll if your SQL server and IIS are on the same machine (a real performance booster)!