SYMPTOMS
On a computer that is running SQL Server, if the computer
has more than 4 GB of physical RAM, SQL Server Enterprise Manager (SEM) shows
the memory settings incorrectly.
To view the memory settings, select
Server, click
Properties, and then click the
Memory tab. You will see that you cannot change the memory settings from
Use a fixed memory size (MB) to
Dynamically configure
SQL Server memory because both options appear dimmed. Under the
dynamic memory settings, the value range of
Minimum (MB) and
Maximum (MB) is 0 MB to 0 MB, and they appear dimmed. Under the fixed memory
settings, the value range for fixed memory is 16 MB to 0 MB.
The
memory settings display incorrectly under the following conditions:
- Microsoft Windows NT 4.0-based computers: The problem
occurs on all editions if the server has more than 4 GB of physical RAM. The
maximum physical memory supported is 4,096 MB.
- Microsoft Windows 2000 Server Advanced Server and
Datacenter Server-based computers: If the server has more than 4 GB of physical
RAM, and if the server is configured to use the Physical Address Extension
(PAE) that enables support of up to 8 GB of physical memory for Windows 2000
Advanced Server and up to 32 GB of physical memory for Windows 2000 Datacenter
Server for applications running on most 32-bit (IA-32) Intel Pentium Pro and
later platforms.
WORKAROUND
To work around the problem, do not view or change the memory
settings by clicking,
Server,
Properties, and the
Memory tab. Instead, to change the memory settings only use Transact-SQL
syntax by running the
sp_configure stored procedure through either the Query Analyzer or the
isql command line utility. To modify the memory settings, use the
following commands:
USE master
go
sp_configure 'show advanced options', 1
go
-- To view the current settings
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
-- To modify the current settings, change the option that you want
sp_configure 'min server memory', 'new value'
go
sp_configure 'max server memory', 'new value'
go
RECONFIGURE WITH OVERRIDE
go
-- Note: To use a fixed memory size set min server memory = max server memory
Server memory settings are an advanced option. If you are using the
sp_configure system stored procedure to change the setting, you can change the
memory settings only when the
Show Advanced option is set to 1. Changes to the Server memory settings take
effect immediately and they do not require you to restart the server.