How to fix System.OutOfMemoryException when using SSMS

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 ...

Read More on Powershell SQLCMD, DBA Tools, SSMS without a splash screen grpahic

Powershell : SQLCMD and invoke-expression

SQL Server DBA Tools

SQL Server – Open SSMS without the splash screen graphic