[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • 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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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