SYMPTOMS
When you use Microsoft SQL Server 2000 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 2000 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 1aspnet_wp.exe (PID:
<PIDNumber>) was recycled
because memory consumption exceeded the
<SizeLimit> MB
(
<Percentage> percent of available RAM).
Event 2A 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:
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
For more
information about the Report Processor component, visit the following Microsoft Developer
Network (MSDN) Web site:
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.
|
| Microsoft Excel | Renders a report in Excel |
| Image (TIFF) | Renders a report as a static image in a page-oriented format |
| PDF | Renders a report in Portable Document Format (PDF) |
| HTML | Renders the report in HTML to a browser |
| CSV | Renders a report in comma-delimited format; the report opens in a
viewing tool that is associated with CSV file formats |
| XML | Renders a report in XML; the report opens in a browser
Note If an XSLT transformation is not applied, this format will
consume less memory than the CSV format consumes. |
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.
MORE INFORMATION
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):
w3wp!library!1b3c!07/11/2005-10:38:00:: e ERROR: Found
System.OutOfMemoryException exception:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. --->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception
of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. ---> System.OutOfMemoryException: Exception of type
System.OutOfMemoryException was thrown.
w3wp!library!1b3c!07/11/2005-10:38:00:: e ERROR: Terminating worker
process
Note By default, the
ReportServerService_
<timestamp>.log file is
in the following location:
\Microsoft SQL Server\<Instance of SQL Server>\Reporting Services\LogFiles
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:
DateTime:
TimeUser: N/A
Computer:
ComputerNameDescription:
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 1aspnet_wp.exe (PID:
<PIDNumber>) was recycled because
memory consumption exceeded the
<SizeLimit> MB (
<Percentage>
percent of available RAM).
Event 2A 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:
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:
ReportingServicesService!library!618!7/9/2003-16:06:01::
Status: Error: Exception of type System.OutOfMemoryException was thrown.
ReportingServicesService!notification!618!7/9/2003-16:06:01:: Error thrown
by delivery provider: System.OutOfMemoryException: Exception of type
System.OutOfMemoryException was thrown.
ReportingServicesService!notification!618!7/9/2003-16:06:01:: Notification
380e6cd2-3e3d-4549-9ed5-9fb6b42266b6 completed. Success: False, Status: Error:
Exception of type System.OutOfMemoryException was thrown., DeliveryExtension:
Report Server Email, Report: Invoice, Attempt 0
ReportingServicesService!dbpolling!618!7/9/2003-16:06:01::
NotificationPolling finished processing item
380e6cd2-3e3d-4549-9ed5-9fb6b42266b6
ReportingServicesService!servicecontroller!568!7/9/2003-16:09:30:: i INFO:
Memory constraint app domain recycle requested
ReportingServicesService!servicecontroller!568!7/9/2003-16:09:30:: i INFO:
Recycling the service from default domain
ReportingServicesService!dbpolling!568!7/9/2003-16:09:30:: EventPolling
polling service stopped
ReportingServicesService!dbpolling!87c!7/9/2003-16:09:30:: EventPolling
heartbeat thread exiting for stop.
ReportingServicesService!dbpolling!568!7/9/2003-16:09:30::
NotificationPolling polling service stopped
ReportingServicesService!dbpolling!880!7/9/2003-16:09:30::
NotificationPolling heartbeat thread exiting for stop.
ReportingServicesService!dbpolling!568!7/9/2003-16:09:30:: SchedulePolling
polling service stopped
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:
ReportingServicesService!reportrendering!17a8!09/28/2005-16:10:12::
e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown., ; Info:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. --->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: Exception
of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown. ---> System.Threading.ThreadAbortException: Thread was being
aborted.
ReportingServicesService!runningjobs!17a8!09/28/2005-16:10:12:: i
INFO: CancelableJobExecution.Execute caught some other thread abort exception
ReportingServicesService!library!17a8!09/28/2005-16:10:12:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!runningjobs!1810!09/28/2005-16:10:12:: i INFO:
CancelableJobExecution.Execute caught some other thread abort exception
ReportingServicesService!library!1810!09/28/2005-16:10:12:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!dbpolling!1810!09/28/2005-16:10:12::
NotificationPolling no longer processing item
6e786bb5-3e4d-462a-92fc-2942e6aec007, will be requeued
ReportingServicesService!dbpolling!1810!09/28/2005-16:10:12:: Queue worker
thread caught unhandled exception: System.Threading.ThreadAbortException:
Thread was being aborted.
at
Microsoft.ReportingServices.Library.NotificationQueueWorker.HandleNotification(QueueItem
item)
at
Microsoft.ReportingServices.Library.NotificationQueueWorker.QueueWorker(QueueItem
item)
at
Microsoft.ReportingServices.Library.QueuePollWorker.WorkItemStart(Object state)
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.