insufficient memory available

  • Hi,

    We are r facing the problem with our one of instance of SQL server 2000.

    We are frequently getting error of insufficient memory available, in event log

    And Also we getting error Error: - No more memory for stolen pages

    Configuration is

    SQL servers 2000 Ent Edition SP4 fix 2040

    Total Physical Memory: 16 GB

    AWE enabled

    MAX memory configured is 12 GB

    Virtual memory configured 3 GB

    Database size: 250GB

    We checked with performance counter and found free pages value decreases every time and once it is 0 error occurs.

    after googling we found that microsoft intruduced a fix 2187 for it but is it the last option for same.

    Please suggest what we need to check further.

    Regards,
    Shivrudra W

  • OS verison pls.

    Did you grant the lock pages memory to the sql account.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • For sql 2000 run this query

    select * from

    sys.sysperfinfo

    Where counter_name

    in('Target Server Memory (KB)','Total Server Memory (KB)')

    Check the following Counters in perfmon.msc.

    1.Memory: Pages/sec

    Average between 0 and 20

    2.Memory: Available Bytes >

    Less than 20 to 25 percent of installed RAM is an indication of insufficient memory

    3.SQL Server: Buffer Manager: Buffer cache hit ratio >90

    4.SQL Server: Buffer Manager: Page Life Expectancy >300

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • OS version is :-Windows server 2003 R2 SP2 Ent.edition

    I am not getting on :- lock pages memory to the sql account,can you pls describe it more

    as when i seen in group policy for it,it is not showing any account name there by which sqlserver starts.

    Memory: Pages/sec :- yes this count showing value verage between 0 to 10 only

    SQL Server: Buffer Manager: Buffer cache hit ratio :-showing in between 87 to 93.

    Memory: Available MBytes:13676MB as we defined Max memory 12976MB

    SQL Server: Buffer Manager: Page Life Expectancy :-this counter showing avg:361 but some time it decreases upto 61 too.

    Note:- free pages counter is also decreases value upto 10 and when apply drop clean buffer it increases upto 12145

    Regards,
    Shivrudra W

  • Total Srv memory :cntr_value =1661808 and cntr_type = 65536

    Regards,
    Shivrudra W

  • Shivrudra (5/12/2011)


    Total Srv memory :cntr_value =1661808 and cntr_type = 65536

    This indicates that SQL Server is using only 1.6 GB of Memory in spite of the 12 GB Max Memory.

    What is the Target Server Memory Counter Value ?

    1. You need to enable PAE (Physical Address Extension ) to use the 16 GB memory available on the box by OS and SQL Server .

    2. Once you do that enable AWE and Max Memory = 12 GB (Ignore if already done )

    3. Assign Lock Pages in Memory privilege to SQL Server

    http://www.sqlcoffee.com/Tuning05.htm

    4. Increase the Size of the Page file. It is recommended to have a page file of at least 2 to 2.5 time the physical memory on the Box.

    This should fix your issue. If not try the hotfix from MS.

    Thank You,

    Best Regards,

    SQLBuddy

  • Shivrudra (5/12/2011)


    OS version is :-Windows server 2003 R2 SP2 Ent.edition

    I am not getting on :- lock pages memory to the sql account,can you pls describe it more

    as when i seen in group policy for it,it is not showing any account name there by which sqlserver starts.

    Memory: Pages/sec :- yes this count showing value verage between 0 to 10 only

    SQL Server: Buffer Manager: Buffer cache hit ratio :-showing in between 87 to 93.

    Memory: Available MBytes:13676MB as we defined Max memory 12976MB

    SQL Server: Buffer Manager: Page Life Expectancy :-this counter showing avg:361 but some time it decreases upto 61 too.

    Note:- free pages counter is also decreases value upto 10 and when apply drop clean buffer it increases upto 12145

    Is it 64 bit or 32 ?

    lock pages memory Look at the Glenn's blog[/url]

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Hi SQLBuddy...Thanks....for replying...

    I have taken sunday downtime to implement it...lets see

    Regards,
    Shivrudra W

  • Hi,Is the issue resolved? if yes could you tell me the approach that you have taken to resolve. Thanks in advance

    Sharth.chalamgari

Viewing 9 posts - 1 through 8 (of 8 total)

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