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

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:

References

• Building Database Objects with Common Language Runtime (CLR) Integration 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
• 960922 Mere Query timeout can lead to fullscale SQLCLR AppDomain unload in some conditions. 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
• 271624 INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage 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