asked
Marked for unload due to memory pressure
Hi, Could you please help me in getting rid over following error that I am getting :- 2011-04-05 08:16:32.31 spid1s AppDomain 70 (SMS_K01.dbo[runtime].69) is marked for unload due to memory pressure. 2011-04-05 08:16:32.49 spid15s AppDomain 70 (SMS_K01.dbo[runtime].69) unloaded. 2011-04-05 08:17:02.70 spid243 AppDomain 71 (SMS_K01.dbo[runtime].70) created. Details of the server are as below :- It’s a SCCM server. SQL 2008 SP2 64 Bit. Microsoft SQL Server 2008 (SP2) - 10.0.4272.0 (X64) Jan 5 2011 22:23:43 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM) Total Physical Memory – 6GB Memory allocated for SQL Server – 2.5GB regards, Nitin Please help me.
This is not an error.. This is information, that there was a memory pressure on the server and therefore the server unloaded mentioned app domain to free some memory. When app domains is unloaded, then all the memory associated with it is feed. Then app domain was recreated. This frequently happen when inside CLR method a is allocated a big amount of sequential memory like very big arrays etc. Then SQL server ofter reload the app domain to free up the memory. Mostly you can avoid this by rewriting the CLR code to better work with memory.. Do not use big arrays etc.
Thanks Pavel for you early response. Initially the memory allocated was 2GB for SQL server, later I have added up 500MB more that makes 2.5 GB for SQL server. But within 5 minutes, SQL took entire allocated memory i.e. 2.5GB. this is what I am more scaring. regards, Nitin
SQL Server will take all the memort it can. It is better to cached data in RAM than to access a disk. SQL Server will very quickly use up all the RAM on a system if possible, so I wouldn't worry about it doing that. It is more important to make sure that you have clean code and also an upper limit for SQL server, otherwise the entire system can suffer.
SQL server will always allocate all the available memory for itself if he finds, that it is necessary. SQL server is caching data from disk, when you are operating, so it will consume all the available memory up to the limit you set to speedup operations.
ok..just for information it is SCCM database which using CLR functionality.
you can simply check it whether there are defined some assemblies except Microsoft.SqlServer.Types and CLR functions/procs/trigers which are using them. If so, then it is using specific CLR. but CLR is present throughout whole SQL server.
Are you aware that SQL CLR uses memory from the MemToLeave section of the processes address space? That is basically a section which does not get used for the buffer pool, and is where 'external' objects get their memory from (i.e. extended procs - old style, CLR etc). It's possible that you simply don't have enough space in that area, but without being there it's difficult to get a feel for it. My advice to you would be to search for 'SQL Server CLR MemToLeave' and read up on how it works, and how you might want to set it. Do exercise caution, however, because you are effectively saying 'don't cache data into this bit of memory' which will have an adverse affect on performance.
