I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment.
The server has 36 GB of ram and I have enabled AWE in SQL Server.
I am continuously receiving the error: "There is insufficient system memory in resource pool 'default' to run this query" in the Windows event logs.
I am running perfmon and monitoring the SQL Server Memory Manager counters for 'Target Server Memory' and 'Total Server Memory' as well as the Memory counter for 'Available KBytes'. Currently the 'Total Server Memory' counter is sitting at 5 GB.
I have configured the 'Minimum server memory' to 25 GB and the 'Maximum server memory' to 30 GB in SQL Server. The server is showing 31 GB of available memory, but I continue to constantly receive this error.
The index creation memory is set to 0 KB (dynamic memory) and the Minimum memory per query is set to 1024 KB.
I have experimented with the Minimum memory per query value but it did not stop the error from occuring.
If my server has 31 GB of available memory (20 GB of this are available to SQL Server), why am I receiving this error?
I have spent hours searching in forms, but others who are receiving this error seem to be low on virtual memory, which is not the case for me.
Does anyone have any ideas what could be causing this error?
I have tried restarting SQL Server and even restarting the server itself, but this error always returns within a minute or two.
I am planning on updating to Service Pack 1 as soon as I have approval to do so, but I doubt that will solve my problems.
Any advice would be greatly appreciated.