Max Memory Settings ?? on only 4 Gig

Question

Max Memory Settings ?? on only 4 Gig

mperu99

SSC Enthusiast

Points: 130
More actions
November 29, 2010 at 8:17 am

#229186

I looked in our SQL Server logs and see a 701 Error Severity 17 State 123 at about the same time. It looks like a long running or memory configuration thing. I am guessing i am starved for memory.
We have 4 GB memory on our server and it is 64 bit Windows 2008 Server SR2, SQL Server 2008 SR2. Max memory has some strange number in it, 2147483647. Should I set it to 3 GB - 3,000 MB?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

It is definitely a good idea to limit SQL at least a little when it comes to memory, because it will truly take every ounce that you give it and the OS needs to have a little to keep things moving. 3 GB seems about right on a server that has 4 GB total but really just make sure that it is not allowed to have all 4 GB.

mperu99 SSC Enthusiast Points: 130 More actions · Answer 2

Thank you for your reply, its very much appreciated that one is not just waiting for nothing..

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

As Mike said, Set Max Mem = 3GB = 3072 MB.

Also assign Lock pages in memory privilege to SQL Server and increase the page file size to 8-10GB.

Thank You,

Best Regards,

SQLBuddy

Johan Bijnens SSC Guru Points: 135588 More actions · Answer 4

with 32-bit, it was restricted to 1,7GB by default. One could even use a /3GB parameter at OS level, to have it consume up to 3GB.

Now, with 64-bit, the OS can directly address more than your 4GB, so SQLServer will be able to use it and so will use it if it needs it.

So you need to take "max server memory" into consideration when installing a 64-bit sqlserver instance on a 64-bit OS.

Here's the ref I use: http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me