ASP.Net SQLConnection leaving orphans

[Guest]

Hello, Everyone.

I have a strange one here. I'm a DBA with relatively low program
experience. Recently I was given an assignment to locate why our SQL Server
has "orphaned" connections. The setup we have is an IIS 6 machine that uses
a third party app designed with ASP .NET. The SQLConnection object is used
by the program to connect up to the SQL database. The connection string is
kept in a separate file so the programmers only have to refer to it by name
rather than retyping all the info. They use connection pooling via the
SQLConnection object, but the pooling doesn't seem to work all the time. We
have what seems to be an inordinate amount of connections that aren't
returning to the pool which we have to clean up every night.

I've researched on Google and the programmers say they have done everything
to check the code for bad connection issues. The only thing we can come up
with is that the ASP Worker Thread process doesn't throw some connection
threads back into the pool. We believe that the issue is caused when the
users click the 'X' button on their browser windows instead of hitting the
program's 'LOGOUT' button.

Can anyone shed any further light on this problem? Has anyone else had this
problem before? If so, can anyone point me in the direction of a solution?
The only thing I can think of is throwing a connection timeout value on the
connection, but I'm not sure if it should go on the SQL Server properties,
the ASP .Net SQLConnection properties or the IIS Server properties. I've
heard that if I do the timeout on SQL Server, I won't be able to put that
connection in the pool. Any help would be greatly appreciated.

Thanks!!!

Catadmin

 

[Kevin Spencer]

I noticed a strange absences in your message. You didn't mention whether or
not you had made sure that all Connections are closed when the app is
finished with them. This is one of those issues like "Is the plug plugged
in?" that is overlooked because it is so simple.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Guest]

Sorry.

The programmers say they've closed all the connections after they are done
pulling the data. They also say they've closed the datareader objects. They
say they haven't closed anything within the finalize or destructor method,
the connection string is the same because they are pulling it from the
web.config file, the connection reset value is set to true, they drop any
temporary objects and close all user defined connections.

Of course, this is what they tell me they've checked before and that they
have spent a lot of time going through the code to verify it all. Now it is
being given to me so that I can give them some fresh perspective. Nothing
I'm finding on the internet, though, is a lot different from any of this.

Catadmin

 

[Prodip Saha]

Catadmin,
Information you have provided is not enough to precisely comment on. All
programmers say that- we did it and it is your fault. You have to provide
the code segment that's pulling data from SQL database in order for us (the
programmer community) make any judgement.

ADO.Net does the connection pooling automatically and it reuses the
connections as long as the connection string remain the same between the
calls. Having said that - one must explicitly close the connection in the
same context it was open.

Thanks,
Prodip Saha