insufficient memory to run this query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • john.livermore@inginix.com

    insufficient memory to run this query

    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

    #2
    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
    Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

    Comment

    • John Bell

      #3
      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]


      Comment

      Working...