May 12, 2011 at 4:51 am
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
May 12, 2011 at 5:02 am
OS verison pls.
Did you grant the lock pages memory to the sql account.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 12, 2011 at 5:03 am
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/
May 12, 2011 at 6:27 am
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
May 12, 2011 at 6:31 am
Total Srv memory :cntr_value =1661808 and cntr_type = 65536
Regards,
Shivrudra W
May 12, 2011 at 6:39 pm
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
May 13, 2011 at 2:20 am
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/
May 14, 2011 at 6:12 pm
Hi SQLBuddy...Thanks....for replying...
I have taken sunday downtime to implement it...lets see
Regards,
Shivrudra W
November 18, 2011 at 2:40 am
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