Error 701: “There is insufficient system memory to run this query” this is a common error for those SQL Server DBA who are managing big size of SQL Server.
When your SQL Server database occupied by lots connections and require more memory to serve this request, sometimes you get Error 701.
There are different alternative to solve this problem.
First, Check your SQL Server setting for “min server memory” and “max server memory”. If you found very small difference in both the value, increase your “max server memory”.
Second, Found out long running queries with its memory usage information and if this query in idle states, please verify and kill this process. The database performance optimization is a major thing for memory usages.
Third, Found out index usages for long running queries because without proper indexing your system DISK I/O increase and its directly affect to your memory.
Fourth, Check the size of the Virtual memory paging file and increase the size of this file.
Fifth, Check the size of the “min memory per query” actually it is by default 1024 KB but in rare situations you can decrease the size of this parameter. Actually, this is not advisable, but you can try it.
Sixth, Try to execute this DBCC command and again this is not advisable because it may affect to the overall performance of the server. But you can try this.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.