Monday, March 30, 2015

There is insufficient system memory in resource pool 'internal' to run this query.

Even though you might get the above error message for different occasions, one thing is for sure, it has to do with memory management. :)

SQL Server allows you to limit the maximum memory that can be utilized by its Database Engine. You can use this feature in order to allow the OS to have access to memory that cannot be used by SQL Server and thus ensure stability. However you have to remember to re-evaluate this setting in the event of a memory upgrade on the machine.

You can perform the above action as follows:
Right click on instance name in SSMS - Select "Properties" - Select the "Memory" tab. You then get the below dialog (in this example I have set as maximum server memory 3GB):





































The above method is a way to ensure OS stability by not allowing SQL Server to consume all memory in any case and by leaving an amount of memory available for the OS.

Now, in the case you get the "There is insufficient system memory in resource pool 'internal' to run this query." error you can try the following actions:


Action 1
Run the T-SQL command:
ALTER RESOURCE GOVERNOR RESET STATISTICS


As described on an MSDN Library article, the above command will cause Resource Governor to resets statistics on all workload groups and resource pools including the "internal" resource pool.

Action 2:
If the above action does not solve the issue you can try to free-up more memory and eventually upgrade the memory capacity of the machine.


[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: