This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Error 701: “There is insufficient system memory to run this query” this is a common error for those SQL Server DBAs who are managing the 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 usage.
Third, Found out index usages for long running queries because without proper indexing your system DISK I/O increase and its direct 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 the overall performance of the server.
But you can try this.
123 DBCC FREESESSIONCACHEDBCC FREESYSTEMCACHEDBCC FREEPROCCACHE