• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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