I have a .NET client application running a very large batch of queries. I have SQL Server's max server memory configured, however when I run the query I get several messages like:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
When I look at the server it has 20GB of memory free and SQL Server is only using 4.8GB (and that number is stable).
I've tried putting some GO statements in, but to no avail. I don't know where it is hitting the memory issues. None of the queries return more than a few rows.
How can I get around this memory issue?
I see someone posted a similar issue but I'm not sure how to allow SQL to write the results to a file rather than trying to put it all in the output window.
Responses to comments
- The memory is set to 22528000.
- A user is running a .NET application (which "calls" a SELECT statement) on his desktop. His desktop configuration is i7 processor with 32gb.
- I'm not running it on SSMS.
- The DBMS is SQL server 2012, SP3.
Best Answer
The error you are seeing is being generated at the client end. Typically this error occurs when the client runs out of memory while trying to display or consume the result set. Try running a smaller subset of queries, and ensure the client is properly disposing objects as they are no longer required.