Problem solve Get help with specific problems with your technologies, process and projects.

Releasing memory on the fly

We are running SQL Server 2000 and trying to maintain a 24x7 system. We have at least one process that runs that does not release memory. Is there a way to free up or release memory on the fly so we don't have to shut down SQL or reboot the server?

Having all of your memory allocated is NOT necessarily a problem. If you are running a dedicated SQL Server and it has allocated all of the memory on your system (less 128 MB which is will never encroach on), LEAVE it. It has that memory allocated, because at some point in time, it needed it. The SQL Server still has that memory, because no other process has requested memory from SQL Server. Since memory allocation is an expensive operation, it isn't just going to release the memory back to the OS unless something else needs it because if it needed that memory at some point in time, the assumption is that it will need that memory again. It would be dumb for SQL Server to just give up memory on a whim only to have to take the hit at a later point and reallocate it back again when nothing else needed the memory. One of the biggest operational mistakes that DBAs make is to assume that because SQL Server has allocated all of the memory (minus 128MB), that there is a problem on the system when in fact, your SQL Server is operating the way it was designed and the way that it performs the best. A SQL Server plays VERY well with everything else on a system. It WILL give memory back to the OS, ONLY IF something else needs it. If you collapse back the memory allocated, you are simply losing your data cache and you now have to resort to direct disk hits to retrieve data which is very expensive. By dumping the SQL Server to free up, you are losing all of the advantages to having a SQL Server running at peak efficiency with a maximum of data cache.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.