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 ?
motioneyeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BuggyCoderConnect With a Mentor Commented:
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.