This one is really easy, but I am surprised at the number of people who are unable to troubleshoot it. If you think you are having sql connections not being closed properly, just open up perfmon, go to “.net data provider for sqlserver”, and add “numberofreclaimedconnections”. Basically what this counter shows is the number of connections that were not closed by the application, but were closed by the .net garbage collection process. Microsoft’s explanation is on this page. The same counter works for the oracle client as well (except it’s called “oracle” and not “sql”).
The symptoms of this would be a whole lot of idle connections to sql (or oracle) from the webserver, or errors about not being able to obtain a connection, running out of connections in the pool, etc. You can also see lots of connections in the time_wait status if you run a netstat –an.