SQL Server 2005 Error, There is insufficient system memory to run this query.

Question

Msg 701, Level 17, State 123, Line 1
There is insufficient system memory to run this query.

I have a query that uses Table-Valued function. I am calling the function several times. Does this error mean I am running out of stack space? Or can I tune my SQL box memory settings to fix this? Any ideas?

Answer 1

If it's an enormous query, and/or there are lots of users using the system, each running large queries, this is inevitable.

Unfortunately, there's a known issue with SQL Server where this error happens even when the workload is the same as previously. Stopping/restarting the server, while completely ridiculous, often resolves the issue.

Another thing you could consider is to run the dbcc free*cache statements to clear out the memory taken up by caching.

Answer 2

I have resolved this by redesigning my query to use 'Multi-Statement Function' This type of TVF apparently creates a temporary table on disk, thus does not suffer from running out of memory.

Thanks