SQL Server 2005: There is insufficient system memory to run this query

Solution 1:

The error message "There is insufficient system memory to run this query." refers to Virtual Address Space (VAS) being unavailable and not memory in conventional sense i.e. within the SQL Server process space.

Given that you are only running with 3GB on this server and SQL Server has been assigned up to 2GB, this means the OS and more importantly anything else on the box has less than 1GB to play with. That's not a whole lot of memory.

If this issue is indeed as the result of a memory leak then it is the VAS outside of the SQL Server process space (memToLeave) that is being consumed.

I would suggest using the -g startup parameter to assign more memory to the memToLeave portion.

See the following article for further information:

http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

You may also wish to reduce the max memory setting of SQL Server but I would do this as a last resort.

Solution 2:

I would suggest using the -g startup parameter as well. It seems to work for most people and will probably work for you as well. My only concern would be that the underlying issue may not be resolved. For example if there is a memory leak due to a linked server, and the MTL is increased to 512Mb will it just be a longer period between memory issues? I don't know the answer to that but I tend to agree with UndertheFold in that a perfmon may be a good start.