Jinasena Infotech Customer Support Desk

Symptoms

When you use Microsoft SQL Server 2000 Reporting Services or you use Microsoft SQL Server 2005 Reporting Services, you may receive the following error message:

An internal error occurred on the report server. 
See the error log for more details. (rsInternalError) Get Online Help
Exception of type System.OutOfMemoryException was thrown.

Additionally, you may notice that the following error message is logged in the SQL Server Reporting Services log file, or that the log ends abruptly:

System.OutOfMemoryException: Exception of type System.OutOfMemoryException was thrown.

You may notice that one of the following events is logged in the Microsoft Windows Application log:

Event 1 Event 2 This issue may occur together with one or more of the following symptoms: 

• When you try to display a report, the report is never finished.
• A report appears to stop responding.
• When you try to display a report, a blank screen is displayed.
• You receive the following error message:
• Subscriptions are not delivered.

Cause

This issue occurs because the computer does not have sufficient memory to complete the requested operation.

A limitation in SQL Server 2000 Reporting Services causes certain parts of report processing to be memory bound. For example, query result processing and object model rendering are memory bound.

The computer does not have sufficient memory to complete the requested operation when one or more of the following conditions are true: 

• A report is too large or too complex.
• The overhead of the other running processes is very high.
• The physical memory of the computer is too small.

A report is processed in two stages. The two stages are execution and rendering. This issue can occur during the execution stage or during the rendering stage.

If this issue occurs during the execution stage, this issue most likely occurs because too much memory is consumed by the data that is returned in the query result. Additionally, the following factors affect memory consumption during the execution stage:

• Grouping
• Filtering
• Aggregation
• Sorting
• Custom code

If this issue occurs during the rendering stage, the cause is related to what information the report displays and how the report displays the information. For example, the following factors affect memory consumption during the rendering stage:

• The number and types of controls
• The relationship between the controls
• The formatting
• The amount of data that is displayed

Resolution

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, click the following article number to view the article in the Microsoft Knowledge Base:274750 How to configure SQL Server to use more than 2 GB of physical memory

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.

For more information about the MemoryLimit setting, see the "More information" section.

Method 4

Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services.

Method 5

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

Method A

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. The following table lists several export formats in order from most memory consumption to least memory consumption.

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 drillthrough report to display details.

Additionally, if the purpose of the report is just data collection, you can use other Microsoft products for better performance. For example, you can use Data Transformation Services (DTS) or Microsoft SQL Server 2005 Integration Services.

Example

The following example demonstrates how to resolve this issue. Consider the following example:

• A report that returns 160 pages in Report Manager cannot be rendered in the PDF format and in the Excel format. The report could far exceed 250 pages when an 8.5-by-11-inch page size is used.
• The data source for the report returns 500 megabytes (MB) of data to the report server. Typically, SQL Server 2000 Reporting Services requires two to three times the amount of memory that is used by the dataset. Therefore, SQL Server 2000 Reporting Services requires almost 1.5 GB of memory to render the report.

To resolve the issue in this example, redesign the report so that the report shows summary data only for a limited set of filter values. Additionally, make sure that the aggregation occurs in the database query that pulls the report data and that the aggregation is not in the report itself. These methods help significantly reduce the amount of data that is returned to the report server. Therefore, the report is rendered successfully and more quickly.

Original Source @ Microsoft Support