Marked for unload due to memory pressure

Question

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.

Answer 1

0

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.

answered Apr 05 '11 at 03:02 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

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

Apr 05 '11 at 03:25 AM nitinshetye

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.

Apr 05 '11 at 03:41 AM WilliamD

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.

Apr 05 '11 at 03:42 AM Pavel Pawlowski

ok..just for information it is SCCM database which using CLR functionality.

Apr 05 '11 at 03:54 AM nitinshetye

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.

Apr 05 '11 at 04:28 AM Pavel Pawlowski

(comments are locked)

Answer 2

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.

Marked for unload due to memory pressure

Follow this question

Related Questions