DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 2874903 - Last Review: August 2, 2013 - Revision: 1.0

Symptoms

When you use Microsoft SQL Server Management Studio (SSMS) to run an SQL query that returns a large amount of data, you receive an error message that resembles the following:
An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

Cause

This issue occurs because SSMS has insufficient memory to allocate for large results.

Note SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that 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 that is mentioned in the "Symptoms" section.

Workaround

To work around this issue, 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

Configure the query window to output the query results to a file. A file output uses a minimal amount of memory. This reserves more memory for storing the results set. To make this change, follow these steps:
  1. Right-click the query window.
  2. Click Results to.
  3. Click Results To File.
  4. Run the query, and then select the location in which to save the results file.

Method 3: Use sqlcmd

Use the sqlcmd tool (http://msdn.microsoft.com/en-us/library/ms162773.aspx)  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.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Applies to
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsurveynew kbexpertiseadvanced kbtshoot KB2874903
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support