Generic Solution:
Check the settings for both min server memory (MB) and max server memory (MB). If max server memory (MB) is a value close to the value of min server memory (MB), then increase the max server memory (MB) value.
Check the size of the virtual memory paging file. If possible, increase the size of the file.
For SQL Server 2005:
Install following HotFix and Restart Server.
Additionally following DBCC Commands can be ran to free memory:
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Isn’t this hotfix for 2005?
Correct. Thank you Sharper for pointing out. I will edit my post to reflect it.
Regards,
Pinal Dave
(http://www.squauthority.com)
This kind of error isn’t resolved by SQL Server 2005 SP2.
I’m going to open a microsoft support case to resolve it permanently….
I also have this problem and I’ve applied SP2 already.
Does anyone know if there’s a resolution?
Thanks
Nicolas, Any resolution from microsoft. I have mssql 2005 with sp2 still having this issue, when we ran etl process
Any update helps us a lot
Just want to know if the hotfix for SQL Server SP2 will solve the “There is insufficient system memory to run this query” issue.
Thanks
Hi!
We had exectly the same problem, on Windows 2003 32 bit & SQL 2005 SP2 with AWE enabled, when trying to make Database Integrity Check (Database size 60GB, with one hudge, 22 GB Table. Yeah, I know, it’s another problem and we are working on it :) ) there was “Error 701 There is insufficient system memory to run this query” error. After that, server was runngin slow, with strange errors in log, a lot of CLR errors and so on and the only solution was SQL Server restart. Also during working day, there were a lot of locks and sometimes the only solution was restarting the server, because killing locked processes didn’t helped.
Yestarday I tried to manually configure Max server memory and… IT WORKS! Integrity check works on 60GB database and besides server WORKS MUCH MORE STABLE. It’s strange, but I think that there is some strange memory management error in AWE (or in something related) and it is reflected on complex operations and objects.
I just wanted to thank you for helping with this terrible problem, SETTING MAX SERVER MEMORY WORKS FOR ME.
P.S. the better solution is to migrate to x64 :) but it’s difficult on production server…
Hi, I have the same error; I really worry about it, because some process (importantant process) that run in my applications made the error when they run; it’s really important to solve this issue, I installed sp2 and the problem continue.. I also executed a disk defracmentation and the result no’t change.
Any other idea to solve the problem…
I really apreciate any help
thanks in advance.
Jagustin
Hi,
Im using Windows NT with sql server 6.5.
now i days i facing a problem when i m going enterprise manager and open the database, sql query, or any other option its shows the error msg.
“A Connection could not be establish to Fisher(Data base name). [SQL Server] There is insufficient system momory to run this query”.
i change my system memory update virtual system memory.
Kindly tell me how i resolve this problem
Hi!
We had exactly the same problem, on Windows 2003 32 bit & SQL 2005 SP2 with AWE enabled… Like Gode we configure Max server memory, first day it works but now it crashes again.
I want to ask that what was your maximum server memory, GODE?
If anybody help us, I will be gratefull
Hi team,
I am getting the same error in my sql server. Please assist me to know where do is specify the min and max server memory.
I would be grateful to get this resolved.
Thanks,
Joseph