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

SQL Ram maximum limit

Is there a way I can stop the SQL memory settings going back to the default settings after a reboot of the server. We only have a small sql database and want to allocate 512Mb only and we set this all ok but after a reboot this changes back to the default.
0
CT08
Asked:
CT08
3 Solutions
 
BartVxCommented:
Have you tried the following:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 512;
GO
RECONFIGURE;
GO
0
 
CT08Author Commented:
Yes tried that but after a reboot it goes back to the original size allocation. Is there anyway to script running the above query so that it will run on server boot automatically?
0
 
James MurrellProduct SpecialistCommented:
what version of sql are you using?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
James MurrellProduct SpecialistCommented:
forgot

in most select server in SSMS etc

1. Select properties from the menu

2. Select memory from the options on the left-hand side.

3. Enter the values for minimum and maximum server memory in MB.

4. Click OK to confirm changes.
0
 
CT08Author Commented:
Version : Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

I have tried setting maximum memory both by SQL query and by:

1. Select properties from the menu

2. Select memory from the options on the left-hand side.

3. Enter the values for minimum and maximum server memory in MB.

4. Click OK to confirm changes.

Both of these save the setting until a server reboot
0
 
AlokJain0412Commented:
Hi ,
I Suppose to think you are using Windows server or O/S Below then 2008
Then u go with following way
If not Pls tell us your all configuration Details



Performing the following step of configurations:

1. use master
go

exec sp_configure 'AWE enable', 1
go
exec reconfigure with override
go

use master
go
exec sp_configure 'Max memory setting(MB); 6656
go
exec reconfigure override
go

2. Boot.ini
multi(0) disk (0) rdisk (0) partition(1)\ WINDOWS="Windows Server 2003, Standard" / noexecute = optout / fastdetect / 3GB


3. Reboot the 0/S server and restart the SQL Server 2008 database instance services.


0
 
CT08Author Commented:
All of the above options we correct in setting RAM size limits but I have since found out that a third party piece of software was resetting this on a system reboot via an XML configuration file. It was setting maximum SQL RAM size to 45% of physical memory.

Thanks for all the comments and help
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

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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