469,645 Members | 1,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Memory Running Out

Hi-
We've got an ASP.Net web app that runs off a Microsoft SQL Server
2000 backend. After a few days the SQL server is completely out of
memory and crawls. It looks like there could be some connections that
aren't being closed or something. Is there a good way to figure out
where the problem is. Looking at the current activity in Enterprise
Manager there are a lot of threads sleeping and a few that are
runnable. Any ideas?

Thanks

Jul 23 '05 #1
1 1190
big DWK (da********@davewking.com) writes:
We've got an ASP.Net web app that runs off a Microsoft SQL Server
2000 backend. After a few days the SQL server is completely out of
memory and crawls. It looks like there could be some connections that
aren't being closed or something. Is there a good way to figure out
where the problem is. Looking at the current activity in Enterprise
Manager there are a lot of threads sleeping and a few that are
runnable. Any ideas?


Note that it's perfectly normal for SQL Server to grab as much memory
as possible, since it uses it for cache. So high memory consumption is
not a sympton of a problem in itself. But if you feel that you have
poor performance, then obviously you have something that needs fixing.

As for connections not being closed, again, this may be a non-issue.
Recall that ADO .Net uses connection pooling, and when the application
closes the connection, the connection hangs around for some 60 seconds.
If you use sp_who2, there is a LastBatch column, if there are idle
processes whose LastBatch is hours or even days ago, then there is
something that should be addressed. This could indeeed be due to failing
to close the connection in the ASP .Net code. You should always close
your commands and connections explicitly, and not rely on garbage
collection.

However, is SQL Server is "crawling" this may be due to problems with
poorly written queries and that sort of thing. Running the SQL Profiler
is a way to track down long-running queries. I often look at these sort
of problems with my own procedure aba_lockinfo, which gathers both lock
information and current statement in a snapshot. Look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by jong | last post: by
8 posts views Thread by laniik | last post: by
16 posts views Thread by Justin Lazanowski | last post: by
7 posts views Thread by Jon Trickey | last post: by
6 posts views Thread by hauger | last post: by
18 posts views Thread by jacob navia | last post: by
1 post views Thread by trialproduct2004 | last post: by
6 posts views Thread by Yahya | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.