Clever Geek Handbook

How to definitely resolve this error: .NET Framework execution was interrupted by an escalation policy due to out of memory?

First of all, here's our setup:

  • SQL Server 2014 SP2 Standard Edition 64-bit
  • 128 GB of RAM (maximum allowed by the standard version), of which 120 GB is allocated by SQL Server
  • Currently the server hosts ~ 5000 databases which are all the same (same tables stored by proc, etc) for just 690GB of data (mdf files only)

Now what's going on:

From time to time, after the server has been down for some time, we get this error when doing queries on some databases:

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

This error occurs more often when we are performing an update of all client databases (at function start) with Red Gate SQL Multi Script . Out of 5000 DBS, we have error on 70 of them. Running the update script again, the error happens in parts and so on, until we update all the databases. It's just annoying.

We have this bug for a long time. Our server had 64GB of RAM, so we just added more memory for the max version of SQL Server Standard Editor, but still the error returned after a few days. We think the error could be a symptom of something else.

A few questions that may help you get an answer:

  • Our version of SQL Server is 64-bit, so we think we don't need to deal with virtual address reservation
  • The error also occurs when running from a client application written in PHP / Linux, so we are not talking about the .NET framework of the client code.
  • In our databases, the only use of the .NET framework we do is GROUP_CONCAT , a CLR.NET assembly with custom functions that help us model the GROUP_CONCAT MySQL aggregate. We have a copy of the assembly in each of our 5000 client databases.
  • We already tried to lower the setting max server memory

    (down to 96GB) but we were still getting these errors.

If you need more information, I'll update my question.

+3


source to share


1 answer


It's been 4 months since I tried the fix and I haven't experienced the error yet. However, I don't have an exact explanation for the error, but here's what I've tried and it seems to work:

My guess was that having the same .NET CLR assembly in each of our 5000+ databases could be a problem and increased memory usage for .NET in some way.



  • I created a new database named DotNetClrUtils

  • I copied the .NET CLR assembly for GROUP_CONCAT

    in this database
  • I changed all use GROUP_CONCAT

    throughout our client code and stored procedures to refer to a single instance of the database DotNetClrUtils

    (calling it as follows: DotNetClrUtils.dbo.GROUP_CONCAT_D(col, ',')

    )

That's all, and now the problem is gone!

0


source






More articles:


All Articles