Question: When executing a statement through SSMS returning a large recordset , I’m getting a System.OutOfMemoryException error. The error occurs before the recordset fully returns. Why is this error occurring and what is the workaround?
I’m using SQL Server 2012 Enterprise Edition . On a 64 bit Windows 2008 server.
The full error message is :
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
Answer: 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 resultset is too big it can easily surpsasss the 2 GB SSMS memory limit
There are some workarounds to this limitations and the resulting error message
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. How to use - SQL Server - SQLCMD with screen output and output file - SQL ...