There is insufficient system memory in resource pool ‘default’ to run this query

Posted on : 06-12-2011 | By : Devi Prasad | In : SQL Errors, SQLServerPedia Syndication

Share:

4


When you try to execute a query on SQL Server you get the error: There is insufficient system memory in resource pool ‘default’ to run this query.

This memory problem would also occur in the following cases:

  • While executing a query on SSMS.
  • Trying to connect to a sql server in Sql server management studio
  • Trying to execute a SQL Query using OSQL in the command prompt
  • While executing a stored procedure
  • Using power shell to connect to SQL Server

In most of the cases the query would run successfully for some time and then encounter this error.

Possible reasons for this error:

  • The main reason would be un availability of allocated physical memory to sql server.
  • The ram would be completely used
  • SQL Server’s maximum memory allocation configured value has been reached.
  • Virtual memory is full

Fix for this error:
Try to allocate more memory to sql server server.
Steps to allocate memory are given below:

  • Connect to SQL Server management studio
  • Right click on the server and click on properties
  • Select Memory
  • Increase the value for Maximum Server Memory(in MB).
    SQL Server Properties window

    SQL Server Properties window

Try the following DBCC Commands

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Kill all the unnecessary idle sessions on the server

Reduce the number of users on this server

Check the task manager and check if 100% of physical memory is being used.
If over loading of the server is the case then

  • Remove the applications on the server that are eating up much of the physical memory
  • increase the RAM on the server
  • increase virtual memory on the server

Optionally re start the SQL Server Service

Now check if the query is causing the same error.

Before making any changes to the server understand the pros and cons of the action performed by reading Microsoft documentation clearly.

(Visited 5,391 times, 2 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (4)

[…] whole of the RAM. Following link helps you in configuring the maximum server memory configuration: Configuring Max Memory in SQL Server FURTHER READING :enable xp_cmdshell on SQL ServerEnable OPENROWSETHow to Call a stored procedure […]

A very EASY way I got around this:

I commented out the 2nd half of the query and executed.

Then I commented out the 1st half of the query and executed.

The query was simply too big to execute all at once for my virtual machine.

That was great, saved couple of hours. Thanks

All good stuff. Worth remembering that the Maximum Server Memory is in Mb. The default on my server is 2147483647 – so I think 2000+ terabytes!

Write a comment



Tags: , , ,