Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

.NET Framework execution was aborted by escalation policy because of out of memory. Expand / Collapse
Author
Message
Posted Saturday, November 28, 2009 4:40 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 12:36 PM
Points: 77, Visits: 588
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
Post #825821
Posted Wednesday, December 09, 2009 7:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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?

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #831397
Posted Wednesday, December 09, 2009 9:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, January 15, 2011 2:34 PM
Points: 83, Visits: 118
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);
}

Post #831579
Posted Wednesday, December 09, 2009 10:03 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 12:36 PM
Points: 77, Visits: 588
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:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

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.

Post #831585
Posted Wednesday, December 09, 2009 10:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #831587
Posted Thursday, December 10, 2009 3:13 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 12:36 PM
Points: 77, Visits: 588
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.




Post #832082
Posted Wednesday, December 30, 2009 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 30, 2009 2:47 AM
Points: 1, Visits: 1
Hey Guys,

i am also facing the same problem, any solution ?

Post #840231
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse