Memory Problem, please help

My SQL server has 3.68 G memory in total. It has no other application on it, the server is not budy.

The current memory usage of SQL server stays at 1.8, in sys.dm_os_memory_clerks, I see

MEMORYCLERK_SQLBUFFERPOOL Default 0 0 400 1636152 1636152
MEMORYCLERK_SQLCLR Default 0 720 1088 101888 22512
MEMORYCLERK_SQLSTORENG Default 0 1480 104 4480 4480
MEMORYCLERK_SQLCLRASSEMBLY Default 0 0 0 4208 4208
OBJECTSTORE_LOCK_MANAGER Lock Manager : Node 0 0 1912 0 4096 4096

The problem is I can not run a CLR function which read a xml file from harddisk, it gives error

.NET Framework execution was aborted by escalation policy because of out of memory.

I tried before, if I restart my SQL server, the function works, but what is the problem? it doesn't look like it is wrong that SQL server allocate 1.8 G memory, and the SQLCLR is only using 24M, it said if there is not enough memory, SQLCLR will upload appdmain to release memory, so even SQLCLR can reused the 24M , it should be able to load my file, which is only 1M/

Any idea?

thanks

It looks like you are running 32-bit SQL Server 2005. If so, you need to make sure that your boot.ini file has the /3GB switch in the boot.ini file (at the root of your C: drive). Then you need to enable AWE in SQL Server 2005. I would also suggest that you give the SQL Server Service Account the "Lock Pages in Memory" right.

This will allow SQL Server to use more than 2GB of memory and will allow SQL to control paging itself.

|||

Nice, really helpful, I will try it

thanks!

|||

Hi David,

Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.

Do you see any errors in the SQL Server error log?

regards

Jag

|||

Hi

Not trying to be contentious here but AWE is only useful if your server has more than 4GB of RAM which is not the case here.

Is there any errors in the SQL Server error log.

regards

Jag

|||

The problem you describe is a known bug in SQL Server 2005 SP1 with a hot fix available:

http://support.microsoft.com/kb/928083

However the fix list for SQL Server 2005 SP2 indicates that the fix is included in the new service pack.

-

The KB article suggests that a workaround is to not use the context connection in your CLR code but rather create a standard connection object.

|||

thanks for your suggestions.

My server doesn't have 4G memory, I did think about it when I applied the changes.

My SQL server looks ok now, after reboot that day after applied the changes. It consume less than 150M now.

I do see a lot of message in SQL log, like

Date 09/03/2007 4:03:41 PM
Log SQL Server (Current - 09/03/2007 8:44:00 PM)

Source spid23s

Message
The query notification dialog on conversation handle '{C7311ACC-9ACE-DB11-9441-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;SqlQueryNotificationService-a1e3d318-87b4-4ae6-84e7-8ef2f04241c1&apos;.</Description></Error>'.

not sure what it affects, but it is not good since it logs a dozen entried every minute, any suggestion what causes that?

thanks