SQL 2000 is not using use more then 2GB

  • O.S. : Windows 2003 Standard Edition

    - Memory : 3.75GB

    - boot.ini : /3GB

    SQL Server : 2000(SP3)

    - AWE is NOT enabled.

    - set working set size is set to 1

    - SQL Memory is Dynamically configured : Min : 1.999GB, Max : 2.900 GB

    - Reserve Physical Memory for SQL Server is NOT enabled.

    SQL does not use more then 2GB using above settings. We have tried using different options (e.g. turning awe on, using /PAE, etc)

     

    Any help is appreciated.

  • Manish

    Are you using the Standard or Enterprise Edition of SQL Server 2000?

    John

  • Enterprise version of sql 2000

  • I'd have thought AWE would be needed for this. Did you set AWE on in SQL Server?

  • Yes. We tried AWE but always get following error in the log

    Warning: unable to allocate 'min server memory' of 1999MB.

     

  • Manish

    AWE is only needed for memory above 4GB.  I'm not sure what's causing SQL Server to get stuck at 2GB.  Do you have any other applications running on the server?  If not, try not specifying a minimum or maximum, and let SQL Server manage its memory completely dynamically.

    John

  • turn off the reserve physical memory.

    set max memory = , say, 3.25 gb

    leave min memory

    set awe on

    you are correct that you can use awe with 4 gb ram  but you must remove the /3gb switch and put the /PAE switch in.

    Failing that the /3gb switch will allow you to use about 2.9gb of memory but without the awe on and with dynamic memory

    Think that's pretty well correct - yup just checked a server - 3Gb = no awe ( I don't usualy use the 3gb switch but I just happen to have one handy!!! )

     

     

     

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • A description of the 4 GB RAM Tuning feature and the Physical Address Extension switch

    http://support.microsoft.com/default.aspx?scid=kb;en-us;291988

     

    MohammedU
    Microsoft SQL Server MVP

  • I am confused.

    Link http://support.microsoft.com/default.aspx?scid=kb;en-us;291988 talks about /3GB switch with 2003 standard edition as shown below.

    Caution Microsoft supports using the /3GB switch in Windows Server 2003, Standard Edition in a production environment for use by Active Directory. For other applications, Microsoft supports using the /3GB switch in Windows Server 2003, Standard Edition only in a production environment if the application vendor has tested in this environment and if the vendor is willing to support the customer who is using this functionality. Microsoft Exchange Server 2003 and Microsoft SQL Server 2000 are supported in production using this functionality. Contact your application vendor regarding their application. The /3GB switch can cause some applications to have problems that are related to address dependencies or to a reduction in kernel space. Except in the cases described here, the /3GB switch in Windows Server 2003, Standard Edition is only for development and testing purposes.

    But according to colin Leversuch-Roberts, /3GB is not needed.

    Which one is correct?

  •  

    You should first be aware of the method you are using to determine that "it is not using more than 2gb." Using task manager will not give you an accurate reading. You should look at performance counters or the running values of show config. Also, if it does not need to use more than 2GB, then it will not.

  • There's also a bug in sp4 that restricts awe memory, you must apply a post sp4 fix.

    Read memory use from sysperfinfo table. You must decide which memory method you wish to use to enable sql server to use more memory.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 11 posts - 1 through 10 (of 10 total)

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