SQL 2008 R2 Wont Release Meoory

[100mbs]

Every night when my SQL 2008 R2 does its nightly maintenance task to backup the DB's, it wont release the system memory after it completes.

The Maintenance job is doing:
DB Integrity
Remove Old Backups
Backup DB
Rebuild Index
Shrink DB
Update Statistics

So the next morning the server runs out of resources for SQL or the application to run. I end up have to reboot the server to free up the memory.

Any suggestions?

 

[gmmastros]

SQL Server was designed to use up all the memory on the computer. RAM is MANY times faster than hard disk, and it's in your best interest for the database to be as fast as possible.

Unfortunately, poorly configured servers can run in to memory problems because the operating system wants memory too.

The good news is that you can configure the amount of memory that SQL Server uses. To do this:

Open SQL Server Management Studio.
In the Object Explorer window, right click on the server, and then click properties.
In the "Select a page" window, click Memory, and then configure the maximum amount of memory that SQL Server will use.

Configuring the max memory is not trivial because you need to determine how much memory you want to allocate to SQL Server. This may require some trial and error. The amount you give it depends on the operating system and any other software that may be running on the server. Give it too much memory and you will not have solved your problem. Give it too little and SQL will perform poorly.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I end up have to reboot the server to free up the memory.

There is another way to do this that is many times faster than rebooting.

Open SQL Server Management Studio
Right Click on your server.
Click restart

This will restart the SQL Server service, which means nobody will be able to connect to the server while it is restarting. Restarting the SQL Service will clear out the memory used by SQL server, but it will only take a fraction of the time it takes to reboot a server.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

A better option than restarting the services or the server would be to properly configure SQL Server to use the correct amount of information.

As others have said SQL Server is designed to use all the memory on the system. You have to tell it to use less than all the memory on the system. SQL does this for performance so that it doesn't have to go to the disk to get data. When you rebuild the indexes the data pages are all loaded into the buffer pool (memory) while SQL does what it needs to do. By changing the amount of memory available to SQL Server you'll leave memory on the server for the other apps that need it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site