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

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: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:You may notice that one of the following events is logged in the Microsoft Windows Application log:

Event 1

aspnet_wp.exe (PID:<PIDNumber>) was recycled because memory consumption exceeded the <SizeLimit> MB (<Percentage> percent of available RAM).

Event 2

A process serving application pool 'DefaultAppPool' suffered a fatal communication error with the World Wide Web Publishing Service. The process id was '9132'. The data field contains the error number.

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:
  
  Underlying connection closed
• Subscriptions are not delivered.

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

For more information about the Report Processor component, visit the following Microsoft Developer Network (MSDN) Web site:

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:

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.

When you use SQL Server 2000 Reporting Services, you may experience a memory-related problem that is caused by one of the following error conditions:

• The System.OutOfMemoryException error is raised.
• The memory limits are reached.

The main difference between the error conditions is the level at which the error conditions are caught and handled.

The System.OutOfMemoryException error is an error that is raised by SQL Server 2000 Reporting Services when an operation requests more memory from the system and the system cannot provide the memory. When this error occurs, SQL Server 2000 Reporting Services shuts down the process in an unsafe way. The shutdown is unsafe because SQL Server 2000 Reporting Services shares resources between running requests. Therefore, SQL Server 2000 Reporting Services cannot guarantee that data is still safe for all requests.

The memory limits are a set of settings to help protect the system from potentially destabilizing conditions. For example, the memory limits help prevent a report server process from using too much memory. The memory limits are specified through the MemoryLimit setting and through the MaximumMemoryLimit setting that are defined in the RSReportServer.config file. When the memory limits are reached, errors are raised by the Microsoft .NET Framework before SQL Server 2000 Reporting Services actually runs out of memory.

You can render a report from a Web site or from a scheduled task. Therefore, the error conditions may occur in two different environments in SQL Server 2000 Reporting Services. The environments are the following:

• The Reporting Services Web service
• The Report Server Windows service

Therefore, the following combinations of error conditions and environments exist.

The Reporting Services Web service and the System.OutOfMemoryException error

Exception messages and shutdown messages that are similar to the following messages are logged in the SQL Server 2000 Reporting Services log file (ReportServerService_<timestamp>.log):Note By default, the ReportServerService_<timestamp>.log file is in the following location: Additionally, events that are similar to the following event are logged in the Application log:

Event Type: Warning
Event Source: W3SVC
Event Category: None
Event ID: 1011
Date: Date
Time: Time
User: N/A
Computer: ComputerName
Description:
A process serving application pool 'DefaultAppPool' suffered a fatal communication error with the World Wide Web Publishing Service. The process id was '9132'. The data field contains the error number.

SQL Server 2000 Reporting Services cannot gracefully handle this error. The Reporting Services Web service catches the exception and shuts down the process. You must resubmit the request.

To resolve this issue, you can add more physical memory to the system or reduce the memory consumption of other processes.

Ideally, you can adjust the MemoryLimit setting or the Application Pool setting of Microsoft Internet Information Services (IIS) 6.0 so that an error is caught before the out-of-memory situation occurs. Therefore, the process is dealt with more gracefully. You must experiment with the settings, and there is no guarantee that you will always reach the memory limits first.

The Reporting Services Web service and the memory limits

Messages are logged in the Windows event log and in the SQL Server 2000 Reporting Services log file. The Reporting Services Web service log file indicates that the process is shutting down. However, SQL Server 2000 Reporting Services does not control the shutdown and cannot log the error information about the shutdown.

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

Event 1

aspnet_wp.exe (PID:<PIDNumber>) was recycled because memory consumption exceeded the <SizeLimit> MB (<Percentage> percent of available RAM).

Event 2

A worker process with process id of '2420' serving application pool 'DefaultAppPool' has requested a recycle because it reached its virtual memory limit.

When the memory limits are reached, Microsoft ASP.NET or IIS 6.0 handles this error and shuts down the Reporting Services Web service. The memory limits are all specified by ASP.NET or by IIS 6.0 and are not controlled by SQL Server 2000 Reporting Services.

You can change the memory limits under the <processModel> section in the Machine.config file.

Note If you are running IIS 6.0 in native mode, the Machine.config file is not used. The memory limits are controlled by properties of the application pool in IIS 6.0.

For more information about memory configuration in ASP.NET and in IIS 6.0, visit the following MSDN Web sites:

• <processModel> element
  http://msdn2.microsoft.com/en-us/library/7w2sway1(vs.71).aspx
  (http://msdn2.microsoft.com/en-us/library/7w2sway1(vs.71).aspx)
• Mapping ASP.NET process model settings to IIS 6.0 application pool settings
  http://msdn2.microsoft.com/en-us/library/aa719566(VS.71).aspx
  (http://msdn2.microsoft.com/en-us/library/aa719566(VS.71).aspx)
• Recycling application pool settings
  http://msdn2.microsoft.com/en-us/library/aa720473(VS.71).aspx
  (http://msdn2.microsoft.com/en-us/library/aa720473(VS.71).aspx)
• IIS 6.0 application isolation modes overview
  http://msdn2.microsoft.com/en-us/library/aa720150(VS.71).aspx?frame=true
  (http://msdn2.microsoft.com/en-us/library/aa720150(VS.71).aspx)

The Report Server Windows service and the System.OutOfMemoryException error

Reports are not delivered to a file share or delivered to the subscribed recipients. When the scheduled render process automatically restarts, the process tries to run the same subscription again as part of the recovery process. Error messages that are similar to the following error messages are logged in the SQL Server 2000 Reporting Services log file:The Report Server Windows service catches the exception and shuts down the process. This is not a graceful process, and any other requests that are in process are lost.

You cannot configure any settings to prevent this issue. This issue is affected by the amount of memory on the computer and by the memory consumption of other processes.

To resolve this issue, you can add more physical memory to the computer or reduce the memory consumption of other processes.

The Report Server Windows service and the memory limits

Reports are not delivered to a file share or delivered to the subscribed recipients. The Status property of a subscription has the "Thread Abort" value. Error messages that are similar to the following error messages are logged in the SQL Server 2000 Reporting Services log file:Note These messages are followed by an abrupt end to the log file.

The Report Server Windows service tries to complete the existing requests. Additionally, the Report Server Windows service monitors its own configuration settings to make sure that a requested operation does not exceed the memory limits. If a requested operation does exceed the memory limits, the Report Server Windows service stops the process. The Report Server Windows service automatically puts the failed jobs in a startup task list. When the Report Server Windows service restarts, the Report Server Windows service tries to run the jobs again.

Two settings in the RSReportServer.config file affect the memory configuration. The specific entries are the MaximumMemoryLimit setting and the MemoryLimit setting in the <Service> tag.

These values represent a percentage of physical memory. If the memory consumption of the existing requests reaches the percentage that is specified by the MemoryLimit setting, the Report Server Windows service stops taking additional requests. However, requests that are currently in progress continue. New requests are accepted again after free memory is less than the percentage that is specified by the MemoryLimit setting.

If the memory consumption of the existing requests reaches the percentage that is specified by the MaximumMemoryLimit setting, the report server application domain is terminated.

These settings mimic the memory limits under the <processModel> section in the Machine.config file and effectively work the same way. These settings lend consistency between the Reporting Services Web service and the Report Server Windows service.

For more information, see the following topics in SQL Server Books Online:

• How to add a drillthrough report link (Report Designer)
• Exporting reports
• RSReportServer configuration file
• Recycling report server application domains