nismor240sx
asked on
Hundreds of Open Sleeping Connections on SQL Server 2005 w/ Classic ASP Application
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.
sample1.jpg
sample2.jpg
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.
sample1.jpg
sample2.jpg
Why cont you try and use connection pooling. Check out this link http://www.15seconds.com/issue/970531.htm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sunithnair, I think I am using connection pooling... I think I am using ODBC.. or at least it seems to be configured in the ODBC Data Source Administrator in Windows 2000 Server control panel. There is a connection pooling tab, it says it is enabled.. also in the article, it seems to reference IIS 3, but we have IIS 5, which seems to have connection pooling enabled by default.
adathelad, one thing I noticed on the ProcessIDs was that when I clicked on them in the Activity Monitor they are mostly blank. Only a few will have the last SQL command in them... and those ones drop off after a minute or so. But it is these blank ones that build up for this database (where I modified the code to close connections). In the other database (where I do not close any connections), that one does not appear to have any blank ones and is now up to about 1700 sleeping processes.
adathelad, one thing I noticed on the ProcessIDs was that when I clicked on them in the Activity Monitor they are mostly blank. Only a few will have the last SQL command in them... and those ones drop off after a minute or so. But it is these blank ones that build up for this database (where I modified the code to close connections). In the other database (where I do not close any connections), that one does not appear to have any blank ones and is now up to about 1700 sleeping processes.
Ok. I believe this is normal behaviour of connection pooling - after the connection is closed by your code, the SPIDs will hang around a bit in SQL Server - when I looked into a similar issue in a .NET environment where we use connection pooling, the SPIDs automatically disappeared typically between 4 and 10 minutes after the connection was closed. When you close a connection when using connection pooling, the connection is actually still kept on the connection pool so that when the same connection string is requested again, it is optimised to pull one from the pool - this makes a good performance improvement. The key thing is to make sure your connection strings to a given database are all exactly the same. If there's any difference at all in the connection string, it will pool a new connection if one with that EXACT connection string isn't available on the pool.
Research I did into memory usage, showed that each SPID was taking up about 80KB of memory on the server...which for 1700 processes would amount to ~133MB.
I know these are links are aimed at ADO.NET, but these are the ones I looked at when researching this issue in my environment. In theory I'd imagine this is not far off the mark for classic ADO:
http://www.developer.com/net/article.php/10916_3729831_3
http://www.pythian.com/blogs/1270/sql-server-understanding-and-controlling-connection-pooling-fragmentation
We too have a large number of SPIDs at any one time, which seem to plateau at a certain point and have come to the conclusion for our environment that this is actually OK.
Research I did into memory usage, showed that each SPID was taking up about 80KB of memory on the server...which for 1700 processes would amount to ~133MB.
I know these are links are aimed at ADO.NET, but these are the ones I looked at when researching this issue in my environment. In theory I'd imagine this is not far off the mark for classic ADO:
http://www.developer.com/net/article.php/10916_3729831_3
http://www.pythian.com/blogs/1270/sql-server-understanding-and-controlling-connection-pooling-fragmentation
We too have a large number of SPIDs at any one time, which seem to plateau at a certain point and have come to the conclusion for our environment that this is actually OK.
This ADO article looks promising:
http://msdn.microsoft.com/en-us/library/ms810829.aspx#pooling2_topic2
Scroll down to Configuring ODBC Connection Pooling section, where it says:
"Within ODBC connection pooling, the following two configurable settings can affect how pooling works. Both can be set by editing the registry directly, but with ODBC 3.5 (MDAC 1.5) and later, you can use the ODBC Data Source Administrator, which is the preferred method.
* The CPTimeout valueThis value indicates the amount of time that a connection remains unused in the pool before the connection is released. (We release the connections to avoid "connection creep," which will eventually lead to the server refusing any new connection requests when it hits its limit on available connections.)
* The Wait Retry valueThis value, expressed in seconds, is the length of time that the connection pool will be blocked when it is determined that the server is not responding."
May be worth trying the CPTimeout value if you're still concerned. Also, might be worth monitoring ODBC connection pooling as detailed further down the article.
http://msdn.microsoft.com/en-us/library/ms810829.aspx#pooling2_topic2
Scroll down to Configuring ODBC Connection Pooling section, where it says:
"Within ODBC connection pooling, the following two configurable settings can affect how pooling works. Both can be set by editing the registry directly, but with ODBC 3.5 (MDAC 1.5) and later, you can use the ODBC Data Source Administrator, which is the preferred method.
* The CPTimeout valueThis value indicates the amount of time that a connection remains unused in the pool before the connection is released. (We release the connections to avoid "connection creep," which will eventually lead to the server refusing any new connection requests when it hits its limit on available connections.)
* The Wait Retry valueThis value, expressed in seconds, is the length of time that the connection pool will be blocked when it is determined that the server is not responding."
May be worth trying the CPTimeout value if you're still concerned. Also, might be worth monitoring ODBC connection pooling as detailed further down the article.
ASKER
I checked all the connection pool settings, they seem to be default. Here is my main concern... the server only runs for about a day and then I have to either restart it or clear the connections and restart IIS. So there has to be a relation to these connections, taking up all the memory is my thought. Now last night, I thought I found the issue, I found a another site of mine that was generating an error, which also had the old global.asa in it that connects to this database. I corrected it and thought that would make sense in regards to emtpy "Last Transact-SQL command batch" processes. Since the dataabase was being opened, but then nothing was running because of the error. But after correcting that and then removing any globa.asa's from all other sites that connect to this database, the empty connections still appear.
Then I took it a step further by starting to STOP all the sites that could possibly be using the connection at all. I stopped pretty much every website that would even have the possibility of connecting to this database. Cleared the connection counter and then saw now only blank ones appearing... seemingly at the same rate.
So is there a way to somehow see in an IIS log or some kind of software or anything, to see what is generating the connection to SQL or to see what page or script might be calling to open the database. Also, shouldn't the "Last Transact-SQL command batch" for the process always have something in it, even with connection pooling? It doesn't seem like it should be blank.
sample3.jpg
Then I took it a step further by starting to STOP all the sites that could possibly be using the connection at all. I stopped pretty much every website that would even have the possibility of connecting to this database. Cleared the connection counter and then saw now only blank ones appearing... seemingly at the same rate.
So is there a way to somehow see in an IIS log or some kind of software or anything, to see what is generating the connection to SQL or to see what page or script might be calling to open the database. Also, shouldn't the "Last Transact-SQL command batch" for the process always have something in it, even with connection pooling? It doesn't seem like it should be blank.
sample3.jpg
ASKER
I have been looking at them for sometime and see that there were a few processes that appeared and were blank... then disappeared after a few seconds. Then there are some processes that appear, which are blank and do not do anything more, just sit and take up space. They remain blank, never displaying anythinng in the "Last Transact-SQL command batch" window.
ASKER
Ok, I found it! It appears there were some global.asa files in some other sites that had the database connection string in it and was opening it, but didn't use it for anything. It must have been left in there for some reason. Anyway, what I did was search my directories for all global.asa files. Then I downloaded them to my computer, opened up the folder containing all the globals in a front page web site, then did a search for source code with part of the connection string. It found about 8 files. I compared those modify dates to those in the search results on the server and modified the globals as needed, either deleting them or removing the sql connection code.
After that, no more blank connections, in fact it has been a whole day almost and there are only 3 connections open right now... during busy times, it may reach 5-10 connections, but that is the most it goes up to.. no more 700-900!!! Thanks everyone!
After that, no more blank connections, in fact it has been a whole day almost and there are only 3 connections open right now... during busy times, it may reach 5-10 connections, but that is the most it goes up to.. no more 700-900!!! Thanks everyone!
ASKER
thank you for kind of getting me in the right direction.. I just did not have an understanding of how it all worked.. now I think I get it completely, which makes it easier to find the problem.