Various memory errors are logged to SQL Server error log when using SQL CLR objects

Article translations Article translations
Close Close
Article ID: 969962 - View products that this article applies to.
Expand all | Collapse all

On This Page

Source: Microsoft Support

RAPID PUBLISHING

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Symptom



When using SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, you many notice one or more of the following error messages being logged in the SQL server error log when running on a 32 bit instance of either SQL Server 2005 or 2008.

Error message 1

AppDomain 2 (xxx) is marked for unload due to memory pressure.

Error message 2

AppDomain Trio.dbo[runtime].xx was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.

Error message 3

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.

Error message 4

Error: 6532, Severity: 16, State: 49. .NET Framework execution was aborted by escalation policy because of out of memory.

Additionally, you may see the above error messages with SQL Server 2008 features which use SQL CLR internally, like DMF (Declarative Management Framework) and spatial data types.

More Information


Error message 1 could just be an informational message indicating that SQL CLR is responding to the memory pressure on the system. If this is an intermittent error message and is not affecting the execution of SQL CLR objects, the message can likely be ignored.

The following steps can help resolve or alleviate the problem:
  • Ensure that your .NET Framework 2.0 is up to date with the latest available patch. Before you update the framework, if your SQL CLR object uses any assemblies other than those documented in Microsoft Knowledge Base article 922672, read through the following KB article to avoid any issues with your SQL CLR objects after the update.

    949080 Error message when you execute a CLR routine or use an assembly in SQL Server 2005: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)" http://support.microsoft.com/kb/949080
  • As with any 32 bit application, SQL server 32 bit instance's virtual memory is limited to 4GB. If you use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, Microsoft recommends that you migrate to a 64 bit SQL Server. This will enable SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment.
  • When you develop custom SQL CLR applications, use SQL CLR memory carefully. Use the following guidelines when designing SQL CLR applications.
    • Avoid caching large amount of data using objects such as DataTables. If you need to access SQL Server data, you use TSQL to retrieve just the data you need in your application.
    • Avoid using static variables in your code to store large objects.
    • Create objects as late, and release them as early, as possible.


If you had followed the above guidelines but continue to receive the errors mentioned in the symptoms section and cannot immediately migrate to 64 bit severs, you can try giving more memory to SQL CLR by increasing the 'MemToLeave' or 'Non buffer pool memory' to 384 MB. This can be done by adding the -g384 switch to the startup parameters under the Advanced tab of SQL server instance properties (http://msdn.microsoft.com/en-us/library/ms345416(SQL.90).aspx) in SQL Server configuration manager.

Please be advised that you should not increase the MemToLeave value arbitrarily. Setting this value too high may have an adverse impact on buffer pool memory usage, which could lead to performance issues with the SQL server instance. Additionally, you may continue to experience SQL CLR memory errors discussed in the article even after adjusting the non-buffer pool memory, in which case further troubleshooting will be needed to identify the root cause.


For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Collapse this tableExpand this table
Rule software

Rule title

Rule description

Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)






SQLCLR needs additional memory configuration








The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations in which the SQL Server instance is configured to use SQL CLR features, but not enough memory is allocated by using the -g startup parameter. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter the warning, "Database Engine - SQLCLR needs additional memory configuration," you need to review the information in this article and adjust the -g startup parameter for this instance of SQL Server to avoid experiencing memory-related problems.
SQL Server 2008
SQL Server 2008 R2








SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)





SQLCLR needs additional memory configuration






The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations in which the SQL Server instance is configured to use SQL CLR features, but not enough memory is allocated by using the -g startup parameter. 

If you run the BPA tool and encounter the warning, "Database Engine - SQLCLR needs additional memory configuration," you need to review the information in this article and adjust the -g startup parameter for this instance of SQL Server to avoid experiencing memory-related problems.
SQL Server 2012









References

DISCLAIMER

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 969962 - Last Review: April 4, 2012 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2005 Compact Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
Keywords: 
kbrapidpub kbnomt KB969962

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com