insufficient memory available

Question

Post reply

insufficient memory available

Shivrudra

SSCrazy

Points: 2835
More actions
May 12, 2011 at 4:51 am

#236096

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

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

OS verison pls.

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

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/

Shivrudra SSCrazy Points: 2835 More actions · Answer 3

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

Shivrudra SSCrazy Points: 2835 More actions · Answer 4

Total Srv memory :cntr_value =1661808 and cntr_type = 65536

Regards,
Shivrudra W

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

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/

Shivrudra SSCrazy Points: 2835 More actions · Answer 7

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

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

Regards,
Shivrudra W

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 8

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

Sharth.chalamgari