Random database connectivity failure

[Guest]

Hello

We have a strange problem that someone maybe could help us with.

We have an ASP.NET application (NET 2.0) that uses Enterprise Library (2.0)
and nHibernate (1.2.0). It’s compiled for any processor and runs in a native
x64 environment (2003 STE, IIS 6). It uses a SQL Server (05, x64, clustered
with active/passive).

The problem is that the SQL-Server in periods throws exceptions that
generate error messages, ADO.NET exceptions. If we use connection pooling we
get the following error:

“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â€.

If we turn off connection pooling we are getting the following exception

“An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)â€

As we can see it, the second exception happens in the both scenarios
(pooling / no pooling), but when we use pooling the first thing that happens
is that the pool gets corrupted and after that the second exception is thrown.

During peak hours the exceptions is occurring within 5,10,15 and 20 minutes
intervals. We have turned off recycling of the worker process and uses inproc
session handling. During low use of the application the error also occurs but
of course not as frequent as under peak hours. We cant find anything
suspicious in the logs (IIS, EventViewer, SQL-Server). If we run performance
counters we don’t se anything indicating memory leaks / connection leaks. And
the thing that’s really confusing is that it only happens in intervals and
that the database connections works most of the time, the error indicates
that the SQL-Server does not allow remote connections, but most of the time
it does?

Before we turned off recycling of the application pool we hade another
strange thing happening. We increased the virtual memory to 1024Mb and turned
of recycling, which caused the worker process of the application to recycle
itself every five minute.

The application is in production environment so we don’t have any big
possibilities to do testing to a large extent.

Regards
Tobias HÃ¥kansson
Varchar AB

 

[William \(Bill\) Vaughn]

Without a detailed study, one can only make an educated guess at what's
going on. I suggest you read my chapter on connecting in my new book or the
white paper on handling the connection pool on my website.

Despite what you report, I still expect that the pool is over-filling. This
can be caused by a number of factors--the most common are not closing
connections and overloading the server. In the latter case, if the operation
requested takes too long to process, the next operation has to open a new
connection instead of inheriting the pooled connection released by the
previous owner. Once the system settles down (user activity slows down),
these connections can be reused by incoming traffic but are not released
immediately. This means that another rush of activity would not cause
problems immediately--only if the volume of work exceeded the server
capacity (again). This would be manifested as surges in the server workload
and exacerbate the problem. This overload condition can be caused by any
number of factors including stored procedures that are too complex--where
their query plan does not match the inbound parameters, queries that perform
more work than the server can handle given the number of requests,
overloading the server with ancillary services like Reporting Services or
using it as a print server... and on and on.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
Between now and Nov. 6th 2006 you can sign up for a substantial discount.
Look for the "Early Bird" discount checkbox on the registration form...
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

We can’t find any problems with the pooling, that it uses to many resources.
The error happens even if we turn of the pooling, that’s the strongest factor
pointing to its not a pool problem. When we use pooling and we run sp_who or
sp_who2 we get 8 waiting connections to our server, the pool size is set to
minimum 8. We are running perf. counters and it seems all ok, but when the
crashes happens we se a dip in the pool counters, it seems like the
application pool is recycled, but nothing in IIS logs indicates that. If we
instead turn on recycling in our current application pool, let us say after
every 1 hour, we see it in our IIS log.

The error occurs even when its not peak hours, say 5 users. We don’t use
store procedures, we uses nHibernate, but nHibernate generates executes much
sql-querys. The server is only used for this web-application, and is pretty
good when it comes to the hardware, 4Gb, Dual Xeon, native x64 environment.

Regards
Erik Forsberg
Varchar AB