How to configure SQL Server with AWE, PAE, /3GB

  • My current production environment has the following setup:

    Windows Server 2003 Standard Edition SP2

    SQL Server 2000 Standard Edition SP4 (only one default instance, none other major applications)

    RAM = 8GB, but at present only 4GB is recognized by OS

    I have read some pros and cons of enabling the PAE and /3GB switch, but some articles were not clear about editions of OS or SQL Server.

    Is it possible to use the PAE, /3GB and AWE in my environment? How can i confirgure it, if someone could provide me with details or links that explain this process in detail.

  • Hello, the first thing we need to do for AWE enable is to run sp_configure and enable advance Options to see AWE, then enable AWE and then set the amount of memory you need to see (in this example is set for 14 GB:

    /************************************/

    sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    sp_configure 'awe enabled', 1

    RECONFIGURE

    GO

    sp_configure 'max server memory', 14144

    RECONFIGURE

    GO

    /************************************/

    Check the ini file to make sure the flag is set by the above script. Regarding the 3GB flag. Read a little bit on BOL about this. It is not a direct answer for that. It depends of a few variables. BOL will give you some of those. For my server I did not set the 3GB flag to allow SQL Server to read 32 GB (Running Win Datacenter, 2K5 Enterprise) But is more a particular setting accordingly to your machine and your needs.

    In any event, either you set 3GB flag or not, the above script will help you out. As always, if you can test this before you apply is better. Not all the networks are the same and it has to match your needs and configuration. Good luck. :hehe:

  • Thanks for your help.

    -- My only concern is that I have 8GB on Ram sitting on that server and only 4GB is recognozed by the OS. Is there a way to bypass this in Windows Server 2003 Standard Edition?

  • SQL Server 2000 Standard Edition can only use a maximum of 2GB of memory (or 3GB if you enable the /3GB switch in the boot.ini).

    No matter what - that is the most you will ever be able to use.

    If you upgrade to SQL Server 2005/2008 Standard Edition - then you will be able to use as much memory as the system has.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • :w00t:Yes, if you use 2005, there is a flag in the server configuration that you set this AWE. No reconfigure needs to be run. Just to make sure it takes the setting, and whenever you have the opportunity to do so, reboot the server or stop and restart the services. For 2000, unfortunately there is no other option :w00t:

  • So i just have to set the /3GB switch and not worry about PAE or AWE setting for my current production environment? And then when we upgrade to SQL 2005, use AWE, right?

    Has anyone actually implemented this and if so, any obvious drawbacks?

  • :hehe: If the upgrade is in-place that is correct. The 3GB flag needs to be researched first for your environment. I do not have that flag set and I have several servers using 14 GB. Checks BOL before you set it up. It has some environmental variables so it might be fine for you and for someone else. PAE will be take care by 2005. 3GB flag is not :hehe:

  • SQL server 2000 standard edition cannot use memory above 2GB AT ALL. there is no point setting any flags in boot ini

    http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx

    ---------------------------------------------------------------------

  • The 2GB limitation is for SQL Server Standard edition on Windows Server 2000. I am using Windows Server 2003!!

  • still applies victor. see

    http://support.microsoft.com/?kbid=274750

    note the section under Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003

    says

    Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options:

    it doesn't mention sql 2000 standard edition.

    ---------------------------------------------------------------------

  • :w00t: They right Victor, I passed the edition. It does not have anything to do with Windows, is a SQL stuff. In order to see more memory, you need to have Enterprise. Sorry if I mislead you before

  • The 2GB limitation is for Standard edition, regardless of the OS.

    The limitations for Enterprise edition vary depending on the host OS.

  • VictorDBA (12/10/2008)


    -- My only concern is that I have 8GB on Ram sitting on that server and only 4GB is recognozed by the OS. Is there a way to bypass this in Windows Server 2003 Standard Edition?

    I agree with all those saying the 2 GB limit applies to your SQL Server edition.

    And in any case, Windows Server 2003 Standard Edition is limited to 4 GB.

  • hey guys this will help u

    find the attachment

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply