First of all, here is our setup:
- SQL Server 2014 SP2 Standard Edition 64-bit
- 128GB of RAM (maximum allowed by Standard Edition), of which 120GB is allocated to SQL Server
- The server currently hosts ~5000 databases which are all similar (same tables, stored proc, etc.) for a total of 690GB of data (mdf files only)
Now what happens:
Every now and then, after the server has been up for some time, we receive this error when executing queries on some databases:
.NET Framework execution was aborted by escalation policy because of out of memory
This error happens more often when we perform an update of all client databases (when we launch a feature) using Red Gate SQL Multi Script. Of the ~5000 DBS, we have the error on 70 of them. Running the update script again, the error happens on a portion, and so on until we have all databases updated correctly. This is just annoying.
We have this error since a long time. Our server had 64GB of RAM, so we just added more memory to max out SQL Server Standard Editor, but still, the error came back a few days later. We think the error might be a symptom of something else.
A few things that might help to get an answer:
- Our version of SQL Server is 64-bit, so we think we don't have to deal with Virtual Address Space Reservation
- The error also happens when running from a client app written in PHP/Linux, so we're not talking about .NET framework of the client code
- In our databases, the only usage of .NET framework we make is GROUP_CONCAT, a.NET CLR Assembly with User Defined Functions which help us simulate MySQL GROUP_CONCAT aggregate. We have a copy of the assembly in each of our 5000 client databases.
- We already tried to lower the
max server memorysetting (to 96GB), but we were still getting those errors
If more info is needed I will update my question.