SQL 2008 R2 - BI runs out of memory

Posted on 2011-10-13
Last Modified: 2012-05-12
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.
Question by:Mayogroup
    LVL 10

    Accepted Solution

    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'

    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')


    Author Comment

    It is 64bit. If i set the limit, will this leave the remaining RAM for running reports?
    LVL 10

    Assisted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now