Issue
Executing SQL query in Microsoft SQL Server Management Studio (SSMS) that returns a large amount of data may result in the following error:
An error occurred while executing the batch. The error message is: Exception of type 'System.OutOfMemoryException' was thrown
Product Line
SQL Server Management Studios
Environment
SQL Server
Cause
This issue occurs because SSMS has insufficient memory to allocate for large results.
Microsoft SQL Server Management Studio (SSMS) is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error 'System.OutOfMemoryException'.
Resolution
Workaround: Try one of the following methods.
Method 1: Output the results as text
Configure the query window to output the query results as text. A text output uses less memory than the grid, and it may be sufficient to display the query results. To make this change, follow these steps:
1. Right-click the query window.
2. Click Results to
3. Click Results to Text.
Method 2: Output the results to a file
1. Right-click the query window.
2. Click Results to.
3. Click Results To File.
4. Run the query, and then select the location to save the results file.
Method 3: Use sqlcmd
Use the sqlcmd tool instead of SSMS to run the SQL queries. This method enables queries to be run without the resources that are required by the SSMS UI. Additionally, you can use the 64-bit version of Sqlcmd.exe to avoid the memory restriction that affects the 32-bit SSMS process.
For more information on sqlcmd Utility use the following link: https://msdn.microsoft.com/en-us/library/ms162773.aspx
