• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

sql2008 freeze due to low memory

I can't login to sql server now due to low memory, when I check the error log it found following:

2012-03-15 07:29:24.12 spid59      Configuration option 'max server memory (MB)' changed from 30000 to 16. Run the RECONFIGURE statement to install.

what I can do now to again login to sql and change memory to bigger size ?
0
motioneye
Asked:
motioneye
1 Solution
 
QPRCommented:
I'd restart the service to unload all currently used memory and then quickly jump in and change the max server memory...

http://msdn.microsoft.com/en-us/library/ms191144.aspx
0
 
BuggyCoderCommented:
somehow the minimum memory assigned automatically by SQL has been assigned in your case.

Use the following:-
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Here is a link that tells you how to modify the same:-
http://msdn.microsoft.com/en-us/library/ms178067.aspx
http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

Here are some other utility links for your help:-
http://msdn.microsoft.com/en-us/library/ms180797.aspx
0
 
sachinpatil10dCommented:
Enable advanced options:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

Open in new window

To set a max server memory limit of 1 GB depending on your RAM:
USE master
EXEC sp_configure 'max server memory (MB)', 1024
RECONFIGURE WITH OVERRIDE

Open in new window

Display new set configuration:
USE master
EXEC sp_configure 'max server memory (MB)'

Open in new window

Disable advanced options:
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Open in new window

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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now