insufficient memory to run this query

[john.livermore]

We seem to have developed a memory leak in our sql server application
and are getting the above error on occasion. Also, over several hours
of hard usage the memory consumed by the sql server ramps up and is
never released. The only thing we have found to remedy the problem is
to stop/start sql server.

My question to the group is, how can I debug this problem? Are there
system stored procedures that would be useful in indentifying any temp
tables, cursors, etc, not getting cleaned up?

Thanks,
John

[Erland Sommarskog]

(jo************@inginix.com) writes:

We seem to have developed a memory leak in our sql server application
and are getting the above error on occasion. Also, over several hours
of hard usage the memory consumed by the sql server ramps up and is
never released. The only thing we have found to remedy the problem is
to stop/start sql server.

My question to the group is, how can I debug this problem? Are there
system stored procedures that would be useful in indentifying any temp
tables, cursors, etc, not getting cleaned up?

There are two completely different issues here. One is that SQL Server
appears to grab all available memory in the machine. This is perfectly
normal. By default, SQL Server allocates as much memory that is available.
This is because, the more data it can have in cache, the better the
performance. The one situation when this does not work well, is when
other applications also are running on the machine. While SQL Server
will yield memory, it may not yield fast enough. So in this situation,
it may be an idea to constrain how much memory SQL Server may use.

The other issue is the error message you get. This message could be the
due to an overload of the server, but it could also be due a bug, stemming
from a particular query that SQL Server does not handle properly.

There are not really very many ways that you can achieve a memory leak
from application programming. I only know of one: failure to call
sp_xml_removedocument once you are done with an XML document. Another
way to waste memory is bad usage of DBCC PINTABLE.

If you search for 701 (which is the error number for the message in
question) in Books Online, you will find a topic which gives some
advice about the error.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi

You don't say what version of SQL Server you are at, if you are not patched
up then you may want to consider doing that. You can monitor connections and
what is happening through profiler and resource usage can be monitored by
performance monitor or task monitor.

Another alternative may be to walk through your code.

SQL Server taking all the memory is not necessarily a problem unless you are
not using the server for the one task. You may want to set a maximum value
for it to use either in the server properties in EM or through sp_configure.

This could be just some poorly written code (which profiler should
highlight) or it could be the Slammer worm it is not necessarily a memory
leak.

John

<jo************@inginix.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

We seem to have developed a memory leak in our sql server application
and are getting the above error on occasion. Also, over several hours
of hard usage the memory consumed by the sql server ramps up and is
never released. The only thing we have found to remedy the problem is
to stop/start sql server.

My question to the group is, how can I debug this problem? Are there
system stored procedures that would be useful in indentifying any temp
tables, cursors, etc, not getting cleaned up?

Thanks,
John