A client was trying to view MS SQL Reportign Services report in the IE browser.
I received the error message looking like this:
That was not much help.
I logged onto my SQL Server 2008R2 and looked in Event Log and I found the following error message:
Exception type: OutOfMemoryException
Exception message: Exception of type ‘System.OutOfMemoryException’ was thrown
Reporting Service was not in running state either
Here what I found Microsoft website 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.
Add sufficient physical memory to the computer.
Schedule reports to run at off-hours when memory constraints are lower.
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.
Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services to allow Reporting Services to utilize more memory.
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.
Export the report to a different format. You can reduce memory consumption by using a different format to display the report.
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.
Here my Action Plan
1. Configure Max server memory for SQL Server
EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'max server memory (MB)', 2048 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
Note: Only 4GB RAM was available
2. Set working set memory in reportsevice config file
Reporting Services can use all available memory, to override default behaviour you need to configure an upper limit on the total amount of memory resources that are allocated to Reporting Services server applications.
WorkingSetMaximum and WorkingSetMinimum define the range of available memory. This setting does not appear in the RSReportServer.config file unless you add it manually.
When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum
Add Line 3 to RSReportServer.config file (Line 1 & 2 should be in the file)
Line 1. <MemorySafetyMargin>80</MemorySafetyMargin>
Line 2. <MemoryThreshold>90</MemoryThreshold>
Line 3. <WorkingSetMaximum>3000000</WorkingSetMaximum>
3. Restart Reporting Service in Configuration Manager
No more OutofMemory errors