locked
Memory problem with SQL Server 2008 R2 RRS feed

  • Question

  • I have recently setup a brand new Windows 2008 R2 server running SQL 2008 R2.  I have a Sharepoint server also running with it and that's all that it does.  The Sharepoint sites that it hosts are nothing big, the largest being 49 GB when backed up.  The other couple are VERY small (less than 1 GB).  The problem I am having is that the server has 16 GB of ram and when I restart the machine on a new day, all runs great.  As the day goes on, the memory in use climbs higher and higher until by the next morning it's maxed out at 16 and all locked up.  I have to restart this machine and then it's fine again, until the next morning.  If I just restart SQL in the services, it knocks the memory usage back down as well... but sometimes if I let it get to high, the only thing I can do is restart it.  Anyone aware of what could be causing this problem?

    Thanks in advance for the help!
    Kimberly Blumberg

    Friday, October 7, 2011 4:26 PM

All replies

  • Hello,

    On 64-bit operating systems, use the “max server memory” to limit memory usage by SQL Server.

    http://msdn.microsoft.com/en-us/library/ms191144.aspx  
     
    Hope this helps.  


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, October 7, 2011 5:08 PM
  • I set this several weeks ago to be 8 GB (in the setting it's in MB so I have it at 8192) and it still continues to use all of my memory.  Is there something else I have to do?

     

    Thanks!

    Friday, October 7, 2011 5:21 PM
  • Hello,

    Please do it with the following statement.

    sp_configure 'max server memory', 8192
    RECONFIGURE

    GO


    This should stop SQL Server from competing for memory.

    Hope this helps.  


    Regards,

    Alberto Morillo
    SQLCoffee.com

     

    Friday, October 7, 2011 6:35 PM
  • I set this several weeks ago to be 8 GB (in the setting it's in MB so I have it at 8192) and it still continues to use all of my memory.  Is there something else I have to do?

     

    Thanks!


    please provide output of
    sp_configure 
    

    and ERRORLOG file http://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Friday, October 7, 2011 6:38 PM
  • This was the output:

    Configuration option 'max server memory (MB)' changed from 8192 to 8192. Run the RECONFIGURE statement to install.

     

    Which log file do you want, the current one?

     

    Thanks!

    Friday, October 7, 2011 7:16 PM
  • If setting the max memory to 8GB still doesn't help, chances are Sharepoint is stealing memory from SQL.  You could try adding the service account to the Lock Pages in Memory local security policy.  This would ensure other applications won't poach memory from SQL.

    Hope that helps,

    John


    John Eisbrener - http://dbaeyes.com/
    Tuesday, October 25, 2011 7:18 PM