Out of Memory exception in SQL 2008

Question

Regarding the Out of Memory exception in SQL Server 2008:

When I execute large query which inserts thousands of rows into a table,

The exception which occurs when executing this is "System.OutOfMemoryException".

Answer 1

According to a really pretty good Microsoft knowledge base article (linked here) this caused by a query which requires more RAM allocated than is available.

In other words, you need more RAM, or you need to redesign the query to break up the process into smaller chunks.

This is a summary of the complete article. The original contains links to content to explain in more detail the process by which you should follow their suggestions.

Blockquote: MS Knowledge Base Article 909678

You may receive the "System.OutOfMemoryException" error message when you use SQL Server Reporting Services

To resolve this issue, use one of the following methods.

Method 1

Add sufficient physical memory to the computer.

Note If you have more than 2 gigabytes (GB) of memory, you can enable the /3GB switch in the Boot.ini file for better performance. For more information about how to use the /3GB switch in SQL Server.

Method 2

Schedule reports to run at off-hours when memory constraints are lower.

Method 3

Adjust the MemoryLimit setting accordingly.

Note: When you render a report through the Reporting Services Web service, the Reporting Services Web service obtains the MemoryLimit setting from the Machine.config file. However, a scheduled report is rendered by the Report Server Windows service. The Report Server Windows service obtains the MemoryLimit setting from the RSReportServer.config file.

Method 4

Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services to allow Reporting Services to utilize more memory.

Method 5

Redesign the report. To do this, use one of the following methods.

Redesign the report queries. You can reduce memory consumption by redesigning the report queries in the following ways:

• Return less data in the report queries.
• Use a better restriction on the WHERE clause of the report queries.
• Move complex aggregations to the data source.

Method B

Export the report to a different format. You can reduce memory consumption by using a different format to display the report.

Method C

Simplify the report design.

You can reduce memory consumption by simplifying the report design in the following ways:

• Include fewer data regions or controls in the report.
• Use a drill through report to display details.