SQL 2008 R2 - BI runs out of memory

We have a BI solution in Place.
SQL Server 2008 R2 for the DB and Sharepoint/Excel Front end.
Server has 32GB RAM, 2 CPU's.

SQL constantly runs at 31.5GB RAM or higher. This causes any reports on the front end to freeze due to insufficient Memory.
The only way to resolve is to restart the server. Which works for a few hours then runs out of Memory again.

I know SQL will use all available RAM available, but shouldn't it release it when it isnt using it? I don't feel having to kick the server every few hours is an adequate solution.
MayogroupAsked:
Who is Participating?
 
Ramesh Babu VavillaCommented:
if your SQL Server 2008 R2 is 64 bit, it consumes all the memory available in the RAM,
if 64 bit  then limit the memory of your SQL Server by using
the below command

EXEC sys.sp_configure N'max server memory (MB)', N'280000'
GO
RECONFIGURE WITH OVERRIDE
GO


in SQL Server 64 bit , total memory is consumed by the buffer manager itself

check it by using
DBCC MEMORYSTATUS  command in management studio or
.

to check your SQL server is 64 bit or 32 bit  run this quuery in management studio

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')


0
 
MayogroupAuthor Commented:
It is 64bit. If i set the limit, will this leave the remaining RAM for running reports?
0
 
Ramesh Babu VavillaCommented:
yes for sure, after setting the memory limit,
open perfmon-> add counter -> memory available MBytes-> and check for available MBytes,if it is less then 10 MB(sugested by microsoft) .then there is a memory leak in your application
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.