Over time, our ASPs are suddenly unable to connect to the SQL2K DB via DSN, apparently because too many sockets get tied up in a LISTENING state. Since a few of our ASP are the product of poor contract work, one suggestion has been that the ASP probably don't clean-up their objects properly. And sure enough, many of the pages do not clean-up their DB connection objects (i.e. "[object].Close...SET [object]=Nothing" is missing from many of the pages). Can this really be a factor? It's strange because we can't seem to duplicate the scenario in a controlled environment AND the problem has only occurred at one of many customer sites. Another suggestion has been that we shouldn't even be using ODBC with ASP and should simply change all ASP to use OLE DB.

Anyone have any advice?

Thanks.

BTW: IIS and SQL2K are running on the same Win2K Svr box.

It is good practice to close your connections and destory them at the end of your script. Not sure if this is what is causing your problem.

Seems strange that its only happening on one of your sites, rather than them all.

Did you attempt to duplicate the problem over the same length of time? or just a short test?

Could it be the customer fooling about?

Does the problem go away when you restart the server? If so a cheap (but a bodge) solution would be just to reboot it periodically.

Yes, it is strange that the problem cannot be duplicated anywhere else.

Yes, rebooting does get the customer up-and-running again. But it just doesn't seem right.

We've been working with Microsoft on isolating the problem, but all we've successfully done so far is use "netstat -an" to determine that the sockets are getting used-up (1000's in the listening state). But we haven't much direction on how to determine what is causing the problem.

We've considered adding the appropriate clean-up to the outsourced ASP. However, since we don't have any proof that the problem is related, it might be viewed as a wasted effort.

We found out that another vendor's system is on the same server as our system and we've cosidered that the problem might be with their software. We just don't have any way of determining where the problem lies. Oh paranoia, how I've missed you.

Thanks for the input. Any other suggestions? Anybody?

You can connect to your SQL db with DSN-less connection and see if you still have the same issue. The DNS-less conection will improve your application performance too.

We actually did that when we thought ODBC's connection pooling was the problem. We changed one of the ASP to use a DSN-less OLE DB connection. At that time the system was "down" because all the sockets were used up, but we still didn't know what was happening. Anyhow, the DSN-less connection would NOT connect when the data-source was set to the server's IP. However, it connected fine when we specified the server's name. This is actually what caused us to start looking at the problem as TCP/IP related.

BTW: There were some SQL aliases, probably the result of the ODBC wizard when creating the DSN. They've all since been removed, in hopes that it would force the use of shared memory as opposed to TCP/IP...but at last check, there were already over 1000 sockets used-up so it's just a matter of time before they run out again.

Thanks.

I would either insist this is the problem directly (which may not be true), or insist that you cannot detirmine the problem with other software working on the server (which is probably true).

Sorry this is not a technical fix - and yes - I agree - fixing the unclosed connection objects may be a waste of time and money. I would tell the clients the later one of the two statements above before spending development time fixing a "possible" problem.

Thanks for the advice...or actually reassurance that someone else out there thinks the way I do.

One thing to metion - we decided that one way to determine if our product is the culprit is by forcing the DSN to use shared memory as opposed to TCP/IP. This will have two benefits, we hope: 1) If they continue to run out of sockets, we will safely assume the problem is not related to our ASP as the DSN no longer uses the TCP/IP library....and....2) WHEN they run out of sockets again, our ASP should be unaffected since they will be connecting via a DSN that uses shared memory as opposed to TCP/IP. I'll try to remember to post our findings.

Again, thanks for the input.

Your welcome; knowing the outcome would be excellent