Getting out of memory errors when creating the system runtime remoting assembly

Question

Getting out of memory errors when creating the system runtime remoting assembly

FLDaveM

SSC Enthusiast

Points: 103
More actions
September 22, 2008 at 12:56 pm

#198633

I'm having a serious SQL CLR issue when I try and run the following
CREATE ASSEMBLY [System.Runtime.Remoting]
FROM
'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Runtime.Remoting.dll'
WITH PERMISSION_SET=UNSAFE
GO
I get one of the 2 following messages:
Msg 701, Level 17, State 123, Line 3
There is insufficient system memory to run this query.
or
Msg 6532, Level 16, State 85, Line 3
.NET Framework execution was aborted by escalation policy because of out of
memory.
We've had the CLR running until this weekend when the SQL machine reported
the backup was not going to happen becuase it was too big. I've created
space on the drive, but still get the error.
4GB RAM, mirrored database
During the execution of the above statement, the cpu hit's 48% and the PF
chart goes from 1.79GB to 1.85GB, still not close to 4GB.
@@version:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 2)
select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from
sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'
7120
0
I've tried rebooting, no help.
I've tried changing the memory from 2147483647 (where did that number come
from??) to 3072MB, no help.
I've tried checking the AWE box, no help.
I've tried DBCC FREESYSTEMCACHE ('ALL'), no help.
The MSDN tech says he'd like me to install the cumulative update #9, but of
course I'd have to do that on the mirror machine as well and get the OK of
the customer and take his system down, so that won't be right away.
I'm stuck at this point, any help would be truly appreciated.
Thanks,
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I am surprised that PSS didn't tell you up front that the assembly you are trying to load is unsupported in SQL Server, and you shouldn't be using it in SQLCLR. What are you doing in SQLCLR that requires Remoting, that you think belongs in the database?

FLDaveM SSC Enthusiast Points: 103 More actions · Answer 2

Thanks for the reply.

When certain items get updated in the database, we want SQL to run some procedures on our server application which then communicates to the client applications via remoting. I may have to ask around if this remoting class assembly really needs to be in SQL I suppose. But, again, it was working before this weekend, and nothing on the server changed except the log file size and the disc space (which I've remedied).

Again, thanks for taking the time to help.

Dave

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Take a look at the SqlDependency Class in .NET 2.0.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

You can register a dependency object in an external app that triggers an event when the data changes in the database. This would be the recommended method for doing what you are after.

SQLCLR does not run in the BPool which is where the configuration changes you mention in your post affect. They run in the MTL/VAS memory space which is much smaller, generally only 256-384MB on most production level SQL Servers with your stated memory sizes. You can increase the size of the VAS allocation but I wouldn't recommend it as a work around to this specific problem, by using the -g startup parameter and setting a value higher than 256 which is the base, generally -g512 seems to resolve MTL/VAS memory issues for SQLCLR since it doubles the base allocation.

However, this specific implementation is misplaced being in SQL Server, and should be abstracted back out to the application tier though the use of a SqlDependency.

FLDaveM SSC Enthusiast Points: 103 More actions · Answer 4

OK, so a reboot may have worked. For example, CLR assemblies missing on Server A. Reboot A which causes a failvoer to B. Still missing from B. Do a failover to A, and there they are.

So, my question is, does this sound like something more than a memory leak? And what tool can I use to profile the database for a leak?

Thanks,

Dave

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

To understand what is happening here, you really have to have a understanding of how the MTL is allocated at startup, and how it is consumed later on. I am working on a Article that covers this indepth after this post and a number of others on other forums. Basically, the MTL is a contiguously allocated portion of the VAS that has a fixed size. A number of things in SQL Server utilize the MTL and on 32 bit servers it is highly susceptable to fragmentation. Xprocs, Linked Servers, OLE Automation calls, and large query plans (> 8KB) can all utilize MTL memory space and could cause it to become fragmented. When you issue the create assembly command for the Remoting DLL, it doesn't just load that assembly. It also has to load all of the dependent assemblies into SQL Server as well. As you can probably imagine, this requires a significant amount of space in the MTL since each assembly is being parsed as it is loaded. If the VAS/MTL allocation is fragmented then there may not be enough contiguous space available for the process to complete.

You would have to know the status of the VAS/MTL region both before and during the execution of the create assembly statement to determine what exactly might have caused the issue loading the assembly. People have this same issue loading System.Web on 32 bit SQL Servers as well. You can find a CTE to monitor this on the following link:

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

One thing to note is that this is rarely if ever a problem if you stick to the approved assembly list for SQLCLR. I think the use of CLR inside SQL and how it is implemented in memory is in its infancy. There are numerous issues that people often encounter when using the approved list involving memory pressure and app domain unloads, and they only get worse when unapproved assemblies get thrown into the mix.

I wouldn't call what you experienced a Memory Leak at all. If you submit it to Microsoft, it will be listed as by design, and it has to do completely with memory allocations by the SQLOS.

Some references on the allocation of the MTL/VAS reserved space in SQL 2005:

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