Fixed memory errors

Question

Fixed memory errors

Jeff W

SSCertifiable

Points: 5207
More actions
January 21, 2004 at 3:02 pm

#101848

I am getting errors in the SQL Server log when I try to use a fixed memory setting.
I have 4 GB of memory and want to allocate 3 GB of it to SQL Server. When I set the fixed memory and re-boot I get this error (on two different Servers) and am wondering if anyone has an idea of what may be causing this...Thanks, Jeff

2003-11-24 21:49:49.18 spid1 Warning: unable to allocate 'min server memory' of 3000MB.

"Keep Your Stick On the Ice" ..Red Green

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

To let SQL Server use the most memory when you have 4GB, you'll need at the Enterprise Edition of SQL Server and an OS (Windows 2003 or Windows 2000 Advanced) that can increase the user-mode address space from 2GB to 3GB. Then you add the /3GB switch to the server's boot.ini file. This allows SQL Server EE to use 3GB. SQL Server allocates whatever memory is available as it can use it, so there is no need to set the 'min server memory', and you shouldn't do so.

--Jonathan

growl Hall of Fame Points: 3068 More actions · Answer 2

Hi Jonathan I have read that setting minimum memory may improve performance on an a dedicated system or is this not the case?

Thanks

jsurmont Valued Member Points: 71 More actions · Answer 3

Is there any reason you need to place restrictions on your SQL server(s)?

In previous versions of SQL Server this may have been appropriate, but with the brains of the SQL 2K engine there really isn't that much of a need for "hand-holding" - come to think of it, I'm trying to figure out why one would need to manually limit memory.

Recommendations:

1. Make your SQL server a dedicated database server. It sounds like there may be several "mouths to feed". SQL needs memory like you and I need air.

2. Let SQL (and the brain's who devised it's engine) do the work of allocating memory resources.

3. Relax and breathe.

JS

Tony Bater SSCrazy Points: 2150 More actions · Answer 4

I am about to get a new server with 5Gb RAM. It will be a dedicated SQL box with W2K-AS and SQL2K-EE installed. I have used the /3GB switch before on a 3.5GB box, but will I need to use AWE to give SQL2K the max amount of room on the new one?

Tony

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

That is not the case. You don't give memory to SQL Server; it takes it.

There are no advantages to setting minimum memory on a server dedicated to SQL server. On a non-dedicated server, it can guarantee that pressure from other programs will never cause SQL Server to relinquish more memory than the minimum, but at the expense of causing paging, which will almost certainly be a greater performance hit than giving up some buffer space.

--Jonathan