SQL Server workaround for System.OutOfMemoryException exception _mssql

When executing a SQL script file with SQL Server 2008 today, there is always an exception that throws the type "System.OutOfMemoryException", which is clearly exported from SQL Server 2008, and should not be wrong. It took a long time to study the problem before it was solved.

The main reason for this error is that the SQL script file is too large and is estimated to exceed 100M, and the solution is to split the script file into several script files and execute them separately.

Solutions from Microsoft's official solution:

Reason:

This problem occurs because the computer does not have enough memory to complete the requested operation.

Restrictions on SQL Server Reporting Services can cause memory bindings to process some parts of the report. For example, query result processing and object model rendering are restricted to memory.

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

1. One report is too large or too complex.
2. The costs of other running processes are very high.
3. The computer's physical memory is too small.

The report is processed in two stages. Two phases are execution and rendering. This problem occurs during the execution phase or during the rendering phase.

If this problem occurs during the execution phase, this problem is most likely due to too much memory consumption of data returned in the query results. In addition, the following factors can affect memory consumption during the execution phase:

1. Grouping
2. Screening
3. Polymerization
4. Sorting
5. Custom code

If this problem occurs during the rendering phase, what information is displayed for the report and how the report displays information.

1. Quantity and type of control
2. The relationships between these controls
3. Format settings
4. Amount of data displayed

Solution:

To resolve this issue, use one of the following methods.

Method 1

Add enough physical memory to the computer.

Note If you exceed 2000 megabytes (GB) of memory you can enable the/3GB switch in the Boot.ini file for better performance. For more information about how to use/3GB switches 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 the reporting schedule to run during off-peak hours when the memory limit is low.

Method 3

The adjustment is set up accordingly.

Note: The Reporting Service Web service is obtained from the Machine.config file when rendered through the Reporting Service Web service. However, the scheduled report renders the Windows service for the report server. The report server's Windows service gets the location from the RSReportServer.config file.

For more information about where you are, see the "More Information" section.

Method 4

Upgrade to the 64-bit version of Microsoft SQL Server 2005 Reporting Service.

Method 5

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

Method A

Re-design the report query. By redesigning the report query, you can reduce memory consumption by using the following methods:
1. Less data is returned in the report query.
2. A better restriction used in the WHERE clause of the report query.
3. Move to a complex aggregation of data sources.

Method B

Export the report to another format. By using different formats to display reports, you can reduce memory footprint. The following table lists several export formats that are sequentially consumed from most memory to the lowest memory consumption.

Copy Code code as follows:

Format description
Reports that Microsoft Excel renders in Excel
(TIFF) Image renders a report as a page-oriented format for a static image
PDF rendering report in Portable Document Format (PDF)
HTML rendering to browser in HTML report
CSV renders the report in a comma-delimited format; Open the report in the viewing tool associated with the CSV file format
XML renders the report's XML; Open the report in the browser

Note: If XSLT transformations are not applied, this format consumes less memory than the CSV format consumes.

Method C

Simplify report design. You can reduce memory consumption by simplifying report design in the following ways:
1. Include fewer data areas or controls in the report.
2. Drillthrough reports are used to display detailed information.
In addition, if the purpose of the report is simply a collection of data, it can be used for better performance with other Microsoft products. For example, you can use Data Transformation Services (DTS) or Microsoft SQL Server 2005 Integration Services.

Example

The following example shows how to resolve this problem. Consider the following example:

1. In PDF format, and in Excel format, you cannot render a report that returns 160 pages in Report Manager. When you use 8.5x11 inch page size, the report may be far more than 250 pages.
2. The report's data source returns 500 megabytes (MB) of data from the report server. Typically, SQL Server 2000 Reporting Service requires two to three times times the amount of memory used by the dataset. As a result, the SQL Server 2000 Reporting Service requires almost 1.5 GB of memory to render the report.

To resolve this problem, in this example, redesign the report to display the summary data for the report to be a limited set of filter values. In addition, make sure that aggregations occur in the report data to extract database queries and the reports in aggregations are not themselves. These methods help greatly reduce the amount of data returned to the report server. Therefore, the report is rendered successfully and faster.