insufficient memory to run this query

[john.livermore@inginix.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

[Erland Sommarskog]

Re: insufficient memory to run this query

(john.livermore @inginix.com) writes:[color=blue]
> 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?[/color]

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_removedo cument 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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Bell]

Re: insufficient memory to run this query

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

<john.livermore @inginix.com> wrote in message
news:1103149262 .666986.4820@f1 4g2000cwb.googl egroups.com...[color=blue]
> 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
>[/color]