March 27, 2007 at 7:46 am
I am running SQL Server 2000 (sp3) and am having issues where something causes our web app to not release the connections causing SQL Server to eventually run out of memory. When the web servers are rebooted, the connections drop to the normal range, but SQL Server doesn't release the memory. Is there a way that I can force SQL Server to release the memory without a restart. This is a production db and I don't want to restart sql server often.
Thanks in advance for any advice.
Keith
March 27, 2007 at 8:27 am
SQL will not give up memory as it is using for the buffers and caches. This is not an issue but can cause some contention becuase of the fact it needed so much memory at one point. Over a period of time as the cache pools resources become unnessary the OS can cause SQL to release.
Sounds to me like maybe the web app is creating connection objects for SQL Server, opening but the code is not closing them due to missing code or mishandled error paths to lead to the closing code.
March 27, 2007 at 9:16 am
How many connections are we talking about? They all use a small amount of memory, but it would have to be thousands and thousands to crash SQL.
You might try some connection pooling on the web app and see if that helps. The other thing is to schedule a job to scan the sp_who results and look for user connections that have not had activity in some time (last_batch, current time difference) and kill them. I used to have jobs that did that in v6.5/1999 for similar problems.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 28, 2007 at 5:26 am
http://dineshasanka.spaces.live.com/blog/cns!22A79FCE82651673!241.entry
My Blog:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply