How to make SQL Server to recognize more than 4GB of memory
By Siva Ram Mosalakanti
Scenario
*********
Operating System: Windows 2000 Advanced Server
Hardware: Compaq DL 580 or any H/W
Existing RAM: 3GB
Upgraded to: 4.5GB on one box and 6GB on other box
Issue: SQL Server doesn't recognize 4.5 GB/6GB of RAM
Solution
*********
Please follow the steps in the same sequence
1) c:\> attrib -s -h boot.ini
2) c:\> edit boot.ini
Add the switches "/3gb and /pae" to this line as:
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000
Professional" /fastdetect /3gb /pae
3) Save the file and close the editor
4) c:\> attrib +s +h boot.ini
5) Reboot the server
6) Login and check the physical memory using right-click on "My
Computer"
icon on desktop and choose Properties. Click on General tab.
7) You should be all set now!!!
Note: Both /3gb and /pae are required as the system has more than 4GB.
8) Make sure you have Windows 2000 latest service pack (SP3);
SQL Server 2000 Service Pack 2 or 3 or 3a.
Now, changes to SQL Server Configurations:
Set the Min Server Memory to 0 MB and Max Server Memory to .
9) Set Min Server Memory and Max Server Memory using these steps:
a) Start SQL Query Analyzer
b) Use Master from the drop-down list box of database
c) sp_configure 'show advanced options', 1 and press F5 (function
key F5)
d) RECONFIGURE and press F5 (function key F5)
e) sp_configure 'min server memory', 0
f) RECONFIGURE and press F5 (function key F5)
g) sp_configure 'max server memory', 2147483647
h) RECONFIGURE and press F5 (function key F5)
10) Enabling AWE using the following commands:
Make sure that the user account the SQL Server service is running
on is added to "Lock Page in Memory policy" of the "User Rights
Assignments" in "Local Security Policy" of the computer. If this
is
not done, please follow these steps
a) Click on Start and choose Programs
b) Choose Administrative Tools, and then select Local
Security Policy.
c) Expand Security Settings
d) Expand Local Policies, and then click User Rights
Assignments.
e) From the screen on the right, right-click Lock pages
in memory,
and then click Security.
f) In the Local Security Policy Setting dialog box, click
Add.
g) Click to select the account where the MSSQLSERVER
service is running.
h) Click OK.
i) Restart the SQL Server service.
k) Start SQL Query Analyzer
l) Use Master from the drop-down list box of database
m) sp_configure 'show advanced options', 1 and press F5
(function key F5)
n) RECONFIGURE and press F5 (function key F5)
o) sp_configure 'awe enabled', 1
p) RECONFIGURE and press F5 (function key F5)
r) Stop and Re-start all SQL Services (SQLServer,
SQLAgent)
Note: Since AWE is a dynamic API, the changes will take place only after
the services are re-started.
11) Make sure the "Working size" is set to 0. If this is not set to 0,
following these commands in SQL Query Analyzer
a) sp_configure 'set working size', 0 and press F5
(function key F5)
b) RECONFIGURE and press F5 (function key F5)
Final Notes
************
a) Please follow the same steps if you have more than 4GB on
any SQL Server 2000 box.
b) Also remember that SQL Server Standard Edition doesn't support more
than 2GB, so the above steps apply only if you are running
Enterprise
Edition of SQL Server 2000.
c) Test the above said changes on a test box. Check the performance
and
then only implement on a production box.
Rafael M. Vilar Bouet
WAN Administrator, Interactive Systems, Inc.
Tel. 787.277.1491
www.ssspr.com
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x61616089.[Email address protected]
Sent: Friday, May 27, 2005 11:19 AM
To: LazyDBA Discussion
Subject: RE: AWE Config Problem
Do you have the /PAE switch set for the O/S so it can see memory above
4gb?
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Egan Kevin
[mailto:mssqldba-ezmlmshield-x13138567.[Email address protected]
Sent: Friday, May 27, 2005 10:10 AM
To: LazyDBA Discussion
Subject: AWE Config Problem
Dear Gurus,
> I have a clustered Win2003 server with 8 GB memory running MSSQL 2000
SP3a
> + MS03-031. AWE has been enabled at the server level but when I
configure
> SQL to use AWE it will not come online. No errors on errorlog but
> following in event log
>
> [sqsrvres] OnlineThread: ResUtilsStartResourceService failed (status
5b4)
> [sqsrvres] OnlineThread: Error 5b4 bringing resource online
>
> Any advice?
>
Kevin Egan
DBA - Capgemini PLC
This message contains information that may be privileged or confidential
and
is the property of the Capgemini Group. It is intended only for the
person
to whom it is addressed. If you are not the intended recipient, you are
not
authorized to read, print, retain, copy, disseminate, distribute, or
use
this message or any part thereof. If you receive this message in error,
please notify the sender immediately and delete all copies of this
message.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the person or entity to which it is addressed. Any
review, retransmission, dissemination, or taking of any action in
reliance
upon this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact
the
sender and delete the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page