.NET Framework execution was aborted by escalation policy because of out of memory.

Question

.NET Framework execution was aborted by escalation policy because of out of memory.

David Wiseman

SSCommitted

Points: 1713
More actions
November 28, 2009 at 4:40 am

#206783

Hi,
We've had this error a few times on our SQL Server:
.NET Framework execution was aborted by escalation policy because of out of memory.
The solution has been to restart the SQL Server service (I tried clearing the cache but this didn't work). I'm having problems finding a solution that will prevent this problem from happening again. I've found a few articles that refer to 32bit servers, but we are running x64. I also found some hotfixes, but these appear to be for SP1/SP2 - We are using SP3. The server has 64GB of memory.
Can anybody offer any advice on this issue?
Thanks,
David
DBA Dash - Free, open source monitoring for SQL Server

Viewing 7 posts - 1 through 6 (of 6 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

How is SQL Server configured to use memory? What is the max/min server memory set at? Is the service account using Lock Pages in Memory? What is the output of SELECT @@VERSION on the server?

sbowell SSChasing Mays Points: 621 More actions · Answer 2

There is a select statement that I have been using to determine whether or not my CLR assembly is taking the mickey with the amount of processing time it is using.

select os.task_address,os.state,os.last_wait_type,clr.state,clr.forced_yield_count

from sys.dm_os_workers os

inner join sys.dm_clr_tasks clr on os.task_address = clr.sos_task_address

where clr.type = 'E_TYPE_USER'

The last column is the important one. If the forced_yield_count is anything but zero then SQL has told the CLR it has to yield its processing. I had a similar problem with a loop I was having to do that took too long to complete. I fixed it by putting in the following lines which yields my thread back to SQL.

nSleepCounter++;

if (nSleepCounter > 5000)

{

nSleepCounter = 0;

System.Threading.Thread.Sleep(0);

}

David Wiseman SSCommitted Points: 1713 More actions · Answer 3

The server is configured to use 56GB (57344MB MAX server memory) of it's 64GB. Min Server Memory is 0. The service account has the lock pages in memory option enabled.

@@VERSION Output:

The server is a dedicated database server - it doesn't perform any other roles. Some other apps do run occasionally though. 7zip is used to compress transaction logs after backup as part of our custom log shipping solution. On a very infrequent basis, we might need to run a SSIS package on our live server. The last SSIS package that was run was about a week before we had the .NET Framework exceptions.

The CLR code we are using inside of SQL is pretty simple - It provides bitwise "SUM/OR" aggregation. e.g. 1 | 2 | 4 = 7. It's possible to do this natively in SQL, but the CLR code runs a lot faster (And it's a lot neater).

Since my initial post we haven't had any more exceptions, but I'm concerned that this will happen again.

DBA Dash - Free, open source monitoring for SQL Server

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

Yielding in CLR is a good thing, as documented by the CLR Integration Team Blog on MSDN:

SQL Server 2005: CLR Integration : Who says tough guys never yield?

By properly yielding in CLR, you work within the cooperative scheduling that SQLOS is designed to do. You also get performance improvements in some cases as shown by their blog.

David Wiseman SSCommitted Points: 1713 More actions · Answer 5

The CLR code we are using is very simple:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

<Serializable()> _

<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native, _

IsInvariantToDuplicates:=True, IsInvariantToNulls:=True, IsInvariantToOrder:=True)> _

Public Structure BitMaskOR

Dim val As SqlInt32

Public Sub Init()

val = 0

End Sub

Public Sub Accumulate(ByVal value As SqlInt32)

val = val Or value

End Sub

Public Sub Merge(ByVal value As BitMaskOR)

val = val Or value.val

End Sub

Public Function Terminate() As SqlInt32

Return val

End Function

End Structure

The code shouldn't be long running - most queries should complete within a few seconds. Also, I wouldn't have thought the memory requirements for this CLR function would be huge. Do you think a thread.sleep statement would make a difference?

Also, if this problem happens again what is the best way to handle it? The stored procedures were failing with the ".NET Framework execution was aborted by escalation policy because of out of memory. ". I tried clearing the cache, but this didn't help. Eventually we decided to restart the SQL Server service - this fixed the problem, but it's not an ideal solution.

DBA Dash - Free, open source monitoring for SQL Server

shoaib_lntech Valued Member Points: 73 More actions · Answer 6

Hey Guys,

i am also facing the same problem, any solution ?