Select the product you need help with

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

Article ID: 969962 - View products that this article applies to.

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.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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
(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

(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

Building Database Objects with Common Language Runtime (CLR) Integration http://msdn.microsoft.com/en-us/library/ms131046(SQL.90).aspx
(http://msdn.microsoft.com/en-us/library/ms131046(SQL.90).aspx)
Using CLR Integration in SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms345136.aspx
(http://msdn.microsoft.com/en-us/library/ms345136.aspx)
960922 Mere Query timeout can lead to fullscale SQLCLR AppDomain unload in some conditions. http://support.microsoft.com/kb/960922
(http://support.microsoft.com/kb/960922)
Find Non-Buffer Pool Memory (MemToLeave) in "Private Bytes" http://blogs.msdn.com/joesack/archive/2009/01/08/find-non-buffer-pool-memory-MemToLeave-in-private-bytes.aspx
(http://blogs.msdn.com/joesack/archive/2009/01/08/find-non-buffer-pool-memory-MemToLeave-in-private-bytes.aspx)
271624 INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage http://support.microsoft.com/kb/271624
(http://support.microsoft.com/kb/271624)
907877 How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 http://support.microsoft.com/kb/907877
(http://support.microsoft.com/kb/907877)

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

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

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback