September 8, 2014 at 10:35 am
Hi,
We started getting these in the SQL error log today:
2014-09-08 16:46:18.06 spid1790 Error: 17803, Severity: 20, State: 12
2014-09-08 16:46:18.06 spid1790 Insufficient memory available..
This is on a Windows 2003 Server 2003 R2 (build 3790)
with 67,108,556 KB RAM.
SQL Server 2000 has been patched up to version 8.00.2249, has AWE enabled and has been configured to lock pages in memory (with max server memory set at 56,320) - so it would seem that KB899761 is not the answer.
DBCC MEMORYSTATUS shows the buffer distribution as:
Stolen23645
Free6719380
Procedures8119
Inram0
Dirty117545
Kept0
I/O1
Latched393
Other339877
So a high figure for "Free". Also Task Manager on the server shows sqlservr only using about 600Mb.
Any ideas or suggestions of how to troubleshoot very gratefully received.
September 13, 2014 at 8:26 am
This actually, as a smarter colleague demonstrated, was caused by a stored procedure call that for some reason needed to be recompiled and was too much for the optimizer. We watched the perfmon counter for optimizer memory climb and climb until the error message came up and the counter fell away. AWE for 32 bit SQL and its use of memory is a new subject for me.Our server shows clear signs of memory pressure regularly.
This forum is very quiet - SQL Server 2000 really is a pain to support.
September 13, 2014 at 9:23 am
Good stuff you figured this out;-) and yes it is getting quieter, which is probably not a bad thing. I for one don't miss 2K although every now and a gain one has to work with it.
Quick thought, don't ever trust the task manager, it's an old lying bugger, a quick search on the subject might be worth it.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply