Reclaiming memory from SQL Server

I have an SQL Server instance who's memory usage gradually grows until windows will give it no more. It seems logical that the occasional big query result would cause the instance to grow.

Is there a way I can convince SQL Server to release the memory it doesn't need any more (other than restarting the service)?

Edit:
I'm using SQL Server 2000 SQL Server 8.00.2039 - SP4 (Standard Edition)

I was able to find that out using the following query:

SELECT 'SQL Server ' 
    + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - ' 
    + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' (' 
    + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

From serverfault CodeSlave

• This is exactly how SQL Server is supposed to work.

  If you have other services on that machine and don't want SQL to consume all the available memory, you will need to set the maximum server memory. See SQL Server Memory Options on MSDN.

  From Portman

• It will only release it if the OS signals that it is RAM starved, or if you stop and restart the service; the thing to do is limit the max amount SQL will use by configuring the 'max server memory' value. If there's nothing else on the server that needs the RAM (and hopefully there isn't) I wouldn't worry about it.

  From SqlACID

• SQL Server will consume memory and not give it back unless it is told by the operating system that there is memory pressure. As Portman has indicated, this is by design, and if you want to limit the memory consumption, you need to set the maximum server memory SQL Server will use.

  From K. Brian Kelley

• Remember that the behavior you're all describing is from SQL Server 2005 onwards, when the memory manager was rewritten to (among other things) respond to memory pressure requests from the OS.

  For SQL Server 2000 and before, once it grabs memory it won't give it back, no matter how much the OS shouts for it.

  CodeSlave - are you running on 2000, 2005, or 2008?

  CodeSlave : Ah ha... yes indeed SQL 2000.

  sh-beta : It doesn't give it back when the OS shouts for it, but it DOES give it back if it sees that the OS is below a certain threshold for free physical memory. See http://msdn.microsoft.com/en-us/library/ms178067.aspx
  From Paul Randal

• The other posters are correct that this is by design, but you absolutely want to limit the max memory to a bit less than your server's RAM. Think about this sequence of events:

  • SQL 2000 runs happily, consuming all of your server's RAM
  • Someone RDPs in, or you have to pull up IE to download a patch, or your backups kick off, whatever
  • SQL has to deallocate and free enough memory for the OS to work
  • Performance sucks while it's freeing memory and paging to disk
  • Things go well enough once it's stable
  • The other operation completes and SQL gradually reclaims the freed RAM
  • Repeat

  To avoid this, configure your max server memory limit to something around 80-90% of your actual physical memory. Instructions for SQL 2000 at: http://msdn.microsoft.com/en-us/library/ms178067.aspx

  Paul Randal : Remember this behavior is 2005 and 2008 - 2000 does not release memory on demand to the OS.

  sh-beta : Not exactly on demand, but it does release memory back to the OS. From the link I posted: " When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. Under Microsoft Windows 2000, SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. Maintaining this free memory prevents Windows 2000 from paging. If there is less memory free, SQL Server releases memory to Windows 2000. If there is more memory free, SQL Server allocates memory to the buffer pool."

  Paul Randal : Ah - true - it does the trick of decommitting a little bit of physical memory - you're right!
  From sh-beta

• So, to summarise the answers:

  There is no way to prompt MS SQL Server to release memory it doesn't immediately need. SQL Server should automatically releasing memory when it required, but not before then. And if your are having memory issues, you should reduce the value of the "max server memory" memory optin.

  From CodeSlave