How can I assign more than 2GB memory to my SQL Server 2008 R2 Web Instance?

Question

I have a Windows 2008R2 Web server running SQL 2008R2 Web 64-bit. The server has 8GB of memory installed. See this from SSMS:

My Server

In the SSMS management interface, I am unable to configure the server to use more than 2GB of memory. I've considered just issuing a sp_configure and see what happens, but I'm a bit flummoxed at the fact that the interface doesn't want to let me assign more than 2GB, so I'm not doing that, yet.

Can't go above 2GB

I can enter a larger number in the box, but it just goes back down to what's shown there.

This server is a small shared hosting server; I'd like to give SQL Server 4Gigs and see how that looks; maybe give it more. Any ideas? Should I just go ahead and do a sp_configure and ignore the silly GUI?

I'm also assuming because it's 64 bit, the AWE box should remain unchecked. — Andrew Barber, Feb 28 '12 at 06:23
Per squillman's answer below, I feel really foolish. Doubly so because the window I've included a screen shot of clearly says the value is in MB! D'oh! — Andrew Barber, Feb 28 '12 at 06:27

score 2 · Accepted Answer · answered Feb 28 '12 at 06:20

2

What value are you putting in the max server memory box? (I'm assuming you are using an account with sysadmin or serveradmin privileges)

If it continues to give you problems I'd just forget about it and use sp_configure like you say. That's what the GUI will do, anyway. If that gives you problems then at least you should be able to see what the error message is. Don't forget to run reconfigure afterward to apply the change.

answered Feb 28 '12 at 06:20

squillman

37,883
12
92
146

I was trying to put double the current value, and noticed as soon as the box loses focus, it changes back. The up/down selector is capped there, too. And yup, I'm trying to do it as an admin. (I can set it to a lower value via that box, for instance). – Andrew Barber Feb 28 '12 at 06:22
1

The value is in MB so for 4 GB max memory you should enter 4000 into that box. – squillman Feb 28 '12 at 06:23
Oh!!! Ok, now I feel foolish! Although, I'm glad I included that photo! – Andrew Barber Feb 28 '12 at 06:25
1

Hey, we alllll been there :) – squillman Feb 28 '12 at 06:26

How can I assign more than 2GB memory to my SQL Server 2008 R2 Web Instance?

1 Answers1