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
Try the following DBCC Commands
[sql]DBCC FREESYSTEMCACHE (‘ALL’)
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS[/sql]
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.