I have an ASP application on Windows Server 2000. Now after about 1 day or so I start getting SQL connection errors and the server appears to be running low on memory. SQL server shows about about 2100 connections for one of our databases and about 700 for another. When it hits this point, everything starts messing up, so I have to either restart the server, or I can also restart sql server and IIS. Then all the memory is released and the application runs normally again.
Originally both database connections were opened via the global.asa, which I have read is a major no-no. Also, none of our pages closed the database connection. So I figured this was the solution. I started working on all the code linked to one of the databases (since it is internal) and rewrote a lot of code and removed the connection from the global.asa and put it into an include.
Also same with the close. Made sure both run. I tested it late at night and it seemed to work, with just myself... so the connections would show 1, then when I logged in or did different things on the server, the connections would go from 1-3 or so, then drop off and go back to 1. During regular hours however that is not the case, it keeps building up, right now it is at 391 and I restarted it already only a 3 hours ago. by tonight it will be at about 700 or so. Which seems exactly the same as it was before, when I was not closing any connections at all... that doesn't seem to make sense. So I am trying to figure out what I am doing wrong here for this database, before I even attempt to mess with the other application related to the other database, which has over 2000 connections.
When I view the connections in SQL 2005 under the Management tools, activity monitor, it shows all these connections as sleeping. So I just don't get it. Any help would be appreciated.
I attached some screenshots if it helps at all.