Tag: Ensure SQL CLR object uses any assemblies other than those documented are not used

There are many error messages defined in SQL Server which are returned when certain invalid operation is performed and based on the task performed, appropriate error number, severity and brief error message is sent to the application which run the query like SSMS or SQLCMD. Unfortunately, not all the error messages are understood easily and can become difficult to identify the cause and to find a solution. Especially errors related to performance or memory can be even difficult to diagnose or troubleshoot. One of the error, which I worked on recently was related to .NET Framework execution was aborted by escalation policy because of out of memory. Initial look at the error message, it given an idea that the error message was result of memory problem, as it is “out of memory” in the error message.

Error: 6532, Severity: 16, State: 49.
–> .NET Framework execution was aborted by escalation policy because of out of memory.
[WARNING] System.Threading.ThreadAbortException: Thread was being aborted.
[WARNING] System.Threading.ThreadAbortException:
[WARNING] at System.Data.SqlTypes.SqlChars.get_Value()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetCharArray()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetString()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetSqlString()
Other than the above error, you may also end up seeing some error messages like below.
– AppDomain 2 (xxx) is marked for unload due to memory pressure.
– 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.
– Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
– 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.

These errors can occur when SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, are used against SQL Server especially on 32 bit SQL instance.
– AppDomain 2 (xxx) is marked for unload due to memory pressure – This error message can be mostly 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 be ignored.
– For other errors, ensure that .NET Framework 2.0 is up to date with the latest available patch. Ensure SQL CLR object uses any assemblies other than those documented are not used. SQL Server 32 bit instance virtual memory is limited to 4GB. If we use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, then the best thing would be to migrate to a 64 bit SQL Server. This will allow SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment. Other than that when we develop custom SQL CLR applications, we need to use SQL CLR memory carefully, like avoiding caching large amount of data using objects such as DataTables, If required use TSQL to retrieve the data, try avoid using static variables in your code to store large objects, and try create objects as late, and release them as early, as possible. If issue still persists, then check if there are any known issues and patch SQL Server to latest version.