Tag: WorkingSetMaximum

System.OutOfMemoryException Error in MS Reporting Services

A client was trying to view MS SQL Reportign Services report in the IE browser.

I received the error message looking like this:

reporting services error - outofmemory

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 information:

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.

Method 1

Add sufficient physical memory to the computer.

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.

 

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