Problem: When executing a query through SSMS you get a System.OutOfMemoryException error
“An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.”
Solution: SSMS is a 32 bit process. It is limited to 2GB memory.When using Grid Mode there is a 64 kb limit per database field. If the result set is too big it can easily surpass the 2 GB SSMS memory limit
Here are some simple workarounds to this memory limitation :
Output the Results as text – Because text output uses less memory than Grid Mode , it may allow you to operate within the memory limit
Output the Results to file – This method uses very little memory.
Use sqlcmd – sqlcmd is a 64 bit application. Meaning it’s memory restriction is far greater than SSMS. Sqlcmd is my best friend – if you know code – you can achieve significantly more in less time.