Oracle connections exhausted by IIS

Question

Our Oracle (9i) DBA called me to report that my IIS (v6) web server has exhausted the available connections.

I created a test page (Classic ASP) that basically creates ADODB.Connection and ADODB.Recordset objects, connects to a database, opens a basic recordset, loops through it, closes the recordset and connection and sets the objects to nothing. This is what most of the applications on the web server do.

While speaking with the DBA I opened the test web page. The DBA was able to see that I connected and noted the ID (I assume this ID helps him track the connection). The page finished loading, therefor, the Close method of both objects have occurred.

The DBA said that the connection was still showing, even after I closed the browser.

Eventually the connection went away, I assume because the Application Pool recycled.

So my question is: Is this normal? Should the connection not go away after the Close method is called.. or at least the session is closed?

I'm not sure if this is helpful or even related, but we've seen these errors periodically in the event viewer on the our web servers:

Event Type: Information
Event Source:   Application Error
Event Category: (100)
Event ID:   1004
Date:       7/21/2010
Time:       7:34:20 AM
User:       N/A
Computer:   VMADE02
Description:
Reporting queued error: faulting application w3wp.exe, version 6.0.3790.3959, faulting module orantcp9.dll, version 9.2.0.6, fault address 0x00005741.

Answer 1

"The page finished loading, therefor, the Close method of both objects must have occurred."

That "must" is actually not the case. The garbage collector runs (and executes your COM objects' destructors), when the application is low on memory, or when it's got some time to kill. Neither of those conditions is guaranteed to happen every time IIS finishes serving a page. Raymond Chen recently wrote a good article about what garbage collection actually means, as opposed to what people think it means. He's talking about .NET, but the same principles apply here. Garbage collection attempts to simulate an infinite supply of memory. It doesn't know enough about Oracle to try to simulate an infinite supply of Oracle connections.

You'll just have to call Close() explicitly.

In .NET, you could use a "using" block, which simulates C++-style stack-object RIIA semantics. But that's no help in classic ASP.

Answer 2

I'm not sure that article is actually that useful since COM uses a reference counting mechanism vs CLRs garbage collector model. That's why he set the objects to Nothing, to explicitly release the object.

Closing a connection when connection pooling doesn't actually close the connection. It merely releases it back to the pool. I have heard stories about connection pooling "gone wrong", so perhaps try disabling it. You could also specify a value for the connection timeout.

As far as I am aware both of these can be set in the connection string for the application.