none
help with this AppDomain event RRS feed

  • 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

    Tuesday, May 27, 2008 2:20 PM

Answers

  • 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

     

     

    Tuesday, May 27, 2008 6:57 PM
    Moderator
  • 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.

    Monday, June 2, 2008 10:06 AM
    Moderator

All replies

  • 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?

    Tuesday, May 27, 2008 3:06 PM
    Moderator
  • 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.

    Tuesday, May 27, 2008 4:30 PM
  • User Defined Types, User Defined Functions, Stored Procedures, Aggregations, Triggers?

     

    Tuesday, May 27, 2008 4:52 PM
    Moderator
  • user defined function

    write in visual c 2005.

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

    Tuesday, May 27, 2008 6:27 PM
  • 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

     

     

    Tuesday, May 27, 2008 6:57 PM
    Moderator
  • 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.

     

    Tuesday, May 27, 2008 9:08 PM
  • 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

     

     

     

     

     

    Tuesday, May 27, 2008 10:30 PM
    Moderator
  •  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?

    Wednesday, May 28, 2008 2:33 PM
  • 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

     

     

    Wednesday, May 28, 2008 10:32 PM
    Moderator
  • 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?

    Thursday, May 29, 2008 5:47 AM
  • 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

     

    Friday, May 30, 2008 2:11 PM
    Moderator
  • 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

    Friday, May 30, 2008 7:20 PM
  • Friday, May 30, 2008 7:23 PM
    Moderator
  • 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.

     

    Saturday, May 31, 2008 6:29 AM
  • My mistake, I didn't catch that you had written STD on your last post.  The OS is your limiter, not SQL Server 2005.

     

    Saturday, May 31, 2008 6:34 AM
    Moderator
  • 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

    Saturday, May 31, 2008 6:41 AM
  • 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.

    Monday, June 2, 2008 10:06 AM
    Moderator
  •  

    The semi colon is required if you are changing your startup paramteres on the service.  Which I'd say is best practice.
    Tuesday, September 30, 2008 3:41 PM
  • 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.
    Wednesday, November 25, 2009 9:06 PM
  • please use semicolon

    Thursday, March 24, 2011 4:42 AM