Article ID: 300964
Article Last Modified on 5/21/2003
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q300964
BUG #: 57935
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.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.
Additional query words: Enterprise Manager Memory PAE
Keywords: kbbug kbpending KB300964
