help with this AppDomain event

Question

Hi,

I've a company that use SQL CLR from IIS that connect to the SQL and insert data to tables.
every few days the server stop responding and i see those error in the sql event viewer
Unsafe assembly 'hebfilts, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 9 (system.dbo[runtime].8).

AppDomain 9 (system.dbo[runtime].8) unloaded.
AppDomain 9 (system.dbo[runtime].8) is marked for unload due to memory pressure.

the problem is fixed only when i restart the sql server service.
now i use SQl Server 2005 SP2 build 9.0.3042
I've found an article that talks about this problem but they say that it fix in build 2153 http://support.microsoft.com/kb/917271
and i'v 3042.
does my build include this fix?(i installed sp2 after RTM)
if so how can i fix this error that i have?

THX

Answer 1

You need to begin to monitor your SQL Server's memory performance counters to isolate what the problem may be.  What kind of items are in the Unsafe assembly?  Is this a 64 bit or 32 bit SQL Server, and how much memory is in the server?

Answer 2

THX for you replay.

what do you mean by "What kind of items are in the Unsafe assembly"?

the server have 4GB MEM and it's 32BIT on Windows Server 2003 STD SP2.

Answer 3

User Defined Types, User Defined Functions, Stored Procedures, Aggregations, Triggers?

 

Answer 4

user defined function

write in visual c 2005.

does my build include the fix that the article talks about?

Answer 5

Run the following query, and paste the output back here:

 

Code Snippet

;WITH VAS_Summary AS

(

SELECT

Size = VAS_Dump.Size,

Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),

Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)

FROM

(

SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],

region_allocation_base_address [Base]

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address <> 0x0

GROUP BY region_allocation_base_address

UNION

SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size],

region_allocation_base_address [Base]

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address = 0x0

)

AS VAS_Dump

GROUP BY Size

)

 

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB],

CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]

FROM VAS_Summary

WHERE Free <> 0

 

 

Answer 6

the output - 20944 4480

the server have 4GB MEM and i removed the /3GB switch in boot ini because i thought that the AppDomain error is because of this.

 

Answer 7

Avi,

 

What this shows is that you have roughly 20MB of memory assigned to the MemToLeave in SQL Server, and of that you only have 4MB available.  This seems odd to me because I thought that the minimum for this was 256MB.  Can you restart your SQL Services and see if the value of the first number changes?

 

The numbers currently provided are a tight pinch for trying to run CLR in SQL, and a known potential problem in 32 bit SQL Servers.  The reason for this is the limitations of 32 bit environments to scale the Virtual Address Space.  On 64 bit servers this number is in the 7-8TB range, although this doesn't equate to actual usuable memory for CLR in SQL.  What is your MaxServerMemory setting for SQL Server currently set to?

 

You can try and set a startup parameter of -g512 for SQL to force SQL to reserve 512MB of memory for the MemToLeave.  This will remove memory from your buffer cache (BPool)allocation, but it should probably resolve your current issue.

 

Do you have a test server that you can try this stuff on?  Also can you provide what your max worker threads is set to?

 

Here are some documents that cover the MemToLeave:

 

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2282951&SiteID=17

http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

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

http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

 

 

 

 

 

Answer 8

Jonathan Kehayias wrote:

Avi,   What this shows is that you have roughly 20MB of memory assigned to the MemToLeave in SQL Server, and of that you only have 4MB available.  This seems odd to me because I thought that the minimum for this was 256MB.  Can you restart your SQL Services and see if the value of the first number changes?   The numbers currently provided are a tight pinch for trying to run CLR in SQL, and a known potential problem in 32 bit SQL Servers.  The reason for this is the limitations of 32 bit environments to scale the Virtual Address Space.  On 64 bit servers this number is in the 7-8TB range, although this doesn't equate to actual usuable memory for CLR in SQL.  What is your MaxServerMemory setting for SQL Server currently set to?   You can try and set a startup parameter of -g512 for SQL to force SQL to reserve 512MB of memory for the MemToLeave.  This will remove memory from your buffer cache (BPool)allocation, but it should probably resolve your current issue.   Do you have a test server that you can try this stuff on?  Also can you provide what your max worker threads is set to?   Here are some documents that cover the MemToLeave:   http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2282951&SiteID=17 http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx http://support.microsoft.com/kb/271624 http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

 

l'll try to restart the sql service tonight.

my MaxServerMemory setting is 2147483647MB(the memory is default after install SQL)

i don't have a test environment.

my max worker threads is set to 0(default)

should i use the /3GB switch in the boot ini?

Answer 9

Prior to migrating to 64 bit, we used the /3GB switch on our servers.  You might consider limiting your max server memory down to 2.5 or 2GB depending on what is running.  Are there any other services running on the SQL Server?  Do you use Full Text Indexing?  SQL Server is a memory hog, so it will take all the memory that it can get.  If you have other things running on the SQL Server other than just the SQL Database Services, you have to restrict your max server memory setting to allow memory for those services to run. 

 

Can you upgrade to more memory, or are you locked into the configuration that you have?  What is your average Page Life Expectancy (should be > 300) in the SQL Server Buffer Manager object of Perfmon, and do you see values on Memory Pages/sec?  Does the Available Memory drop below 50MB?  These are all signs that the server is memory bound, and that you need more memory.  For further information see the following article:

 

http://msdn.microsoft.com/en-us/library/ms176018.aspx

 

 

Answer 10

this is 32BIT server so it can only have 4GB total.

i don't seams to have a memory problem.

other sql queries runs very good and beside this app domain error,the server runs smooth.

i read an article that talks about app domain and sometinh with Hyper Threading,should i disable the HT?

Answer 11

You can expand beyond 4GB on a 32 bit server with PAE and AWE, but if you don't feel that the server itself is memory bound we won't go there.  I can definately see that your MemToLeave area is memory bound, so adding the -g512 parameter should help out there.  Then you can try retesting this since you are allocating much more memory into the MemToLeave area. 

 

As for HyperThreading, that is completely up to you.  I don't use hyperthreading on my sql servers.  Have a look at this article by Kevin Kline:

 

http://sqlblog.com/blogs/kevin_kline/archive/2007/08/18/the-perils-of-hyperthreading-for-sql-server.aspx

 

Answer 12

are you saying that i can add beyond the 4GB memory on 32BIT?

if i have lets say 6GB mem on 32BIT server and add the /PAE does Windows 2003 Server 32BIT and SQL Server 2005 STD 32BIT can handle the 6GB mem?

according to all 32BIT STD Server specification it can handle only 4GB limit.

so what is true?

 

THX

Answer 13

Have a look at the following KB Articles:

 

How to configure SQL Server to use more than 2 GB of physical memory

Enabling AWE Memory for SQL Server

 

 

Answer 14

I'm not sure that you are right about that Server 2003 STD 32BIT can support above 4GB

 

Note The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.

 

Answer 15

My mistake, I didn't catch that you had written STD on your last post.  The OS is your limiter, not SQL Server 2005.

 

Answer 16

ok i've set the -g512 on the sql server startup,at the end of the line i added ;-g512

am i right?

 

second question - should i enable the AWE on the SQL Server that run on Windows Server 2003 STD 32BIT and Sql Server 2003 STD 32BIT?

 

THX

Answer 17

For your first question, did you include the semi colon?  If you did, it should be removed to be like the following:

 

Code Snippet

sqlservr.exe -sMSSQLSERVER -g512

 

 

For the second question, given your configuration, no you should not enable AWE.

 

Once you have the -g option running, you can validate it by running the query above to see if you have 512MB of memory  524288KB in the MemToLeave area.  Then let your CLR run as normal and see if this resolves the unloading issues.

Answer 18

 

The semi colon is required if you are changing your startup paramteres on the service.  Which I'd say is best practice.

Answer 19

Hi Jonathan,

I've a similar issue with my SQL box. It's SQL 2005 ent edition. 9.0.4035. It's x64 with 32gb RAM.

I'm getting the error '.NET Framework execution was aborted by escalation policy because of out of memory.'

I was running an update statement at this time which was calling a function which was using clr.

I've also run your query above and below are the results.

Total avail mem, KB Max free size, KB

8532256848 6661828544

On my server, when I ran the memory consumption report, I see the following big numbers:

Component Type Allocated Mem Virt Mem Reserved Virt Mem Committed AWE mem Allocated

MemoryClerk_SQLCLR 21,520 23,344,384 16,977,220 0

MemClerk,SQLBUFFERPOOL 1,296 33,603,584 32,768 28,785,920

Right now, the server is running fine but the clr is not going to run anymore. I've to restart the server to get rid of the error. the assembly is created as SAFE.

I've also run DBCC FREESYSTEMCACHE('ALL') and the above numbers stayed pretty much the same. SQLCLR virt mem reservation went down by about 300mb and committed mem went down by about 100mb.

Please let me know if something else comes to your mind that we can try.

Thank you.