max_server_memory

[niall5098]

Hi All,

I have a windows 2003 server, 32bit, with 4 GB of RAM. SQL Server had its default value of 2GB. I wanted to add another GB.

I have edited the boot.ini to inlude the 3gb switch.

i then enabled AWE. Then reconfigured the max server memory and rebooted again.

However when i check the max server memory, it is still 2gb.

Commands used:

sp_configure 'show advanced options',1
reconfigure
go

sp_configure 'awe enabled', 1
reconfigure
go


sp_configure 'max server memory', 3072
reconfigure
go

When i run sp_configure i get the following:

awe enabled 0 1 1 1
max server memory (MB) 16 2147483647 3072 3072

Anyone know why the reconfig is not working?

Thanks in advance

 

[Jamfool]

you have set it. the run value and the config value are set to 3gb. the max value that it could be set to is the big number.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/d18b251d-b37a-4f5f-b50c-502d689594c8.htm

Column name Data type Description

name nvarchar(35) Name of the configuration option.

minimum int Minimum value of the configuration option.

maximum int Maximum value of the configuration option.

config_value int Value to which the configuration option was set using sp_configure (value in sys.configurations.value). For more information about these options, see Setting Server Configuration Options and sys.configurations (Transact-SQL).

run_value int Currently running value of the configuration option (value in sys.configurations.value_in_use).

 

[mrdenny]

What version and edition of SQL Server?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

MSSQL 2005 SP2

 

[mrdenny]

Ok, everything looks ok. sp_configure is showing 3 Gigs of memory for the max memory. Where are you seeing that only 2 Gigs is the max?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

when i enter sp_configure, under the heading 'maximum', the default value of 2gb still remains:

name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 3072 3072

 

[mrdenny]

That's 2 PB of ram not 2 Gigs. The config_value and run_value and the numbers that you care about which is 3 Gigs.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Jamfool]

It is the maximum value (2147483647) that can be stored in 32 bit in twos comp.

Data type: int
Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
Storage: 4 Bytes

2 PB would almost fit in this value, not that it really matters as a 32 bit system can not use ram of this size.

2147483647MB
So:
/1024 = 2097,151.9990234375 GB
/1024 = 2047.99999904632568359375 TB
/1024 = 1.99 PB