How to fix System.OutOfMemoryException when using SSMS

29 December,2015 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix System.OutOfMemoryException when using SSMS


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer