App Domain Unload Error in SQL Server 2012

Question

We have a SQL CLR component deployed in 2012, a calling web service. We keep getting the following error message “AppDomain DSLGetDataClient.dbo[runtime].133 was unloaded by escalation policy to ensure the consistency of your application. Application failed to release a managed lock.” Intermittently.

Also there is another issue logged “AppDomain 219 (DSLGetDataClient.dbo[runtime].369) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations”.

The system recovers (app domain loaded again) and starts responding after 30 minutes without any intervention.

Appreciate your pointers.

Answer 1

Hi Harinaravan,

If a CLR routine causes stack overflow or still owns a managed lock upon thread exit, the CLR routine unloads the application domain to help protect the stability of SQL Server. Therefore, this action affects other users who are running the code in the same application domain.

If you open one connection to run a function, and then you open another connection to run the function later, when the second connection unloads the application domain, you will get the error you mentioned.  

If the memory is too small for .NET managed code, a common indicator of this will be the appearance of frequent “Application Domain Unload” messages appearing in the SQL Server Error log. An error message will be throw:
 
AppDomain is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.

You can refer to this blog:
http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/

So, come back to the first error, the second error caused the APPDOMAIN unload, and then the first error happened.

Please first make sure when you are uploading the app domain, there is no other connection and increase the memory if possible.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Harinaravan,

Also suggest you to increase your memory or reduce the memory cost in your machine

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

We have a SQL CLR component deployed in 2012, a calling web service. We keep getting the following error message “AppDomain DSLGetDataClient.dbo[runtime].133 was unloaded by escalation policy to ensure the consistency of your application. Application failed to release a managed lock.” Intermittently.

Also there is another issue logged “AppDomain 219 (DSLGetDataClient.dbo[runtime].369) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations”.

The system recovers (app domain loaded again) and starts responding after 30 minutes without any intervention.

Appreciate your pointers.

This is by design - bear in mind though the phrase "by escalation policy" - I've read in several places that there is an actual policy that can be configured but there is no reference to this in any published MS documentation so I have no real idea if it is true.

Be sure to read and understand ALL of the restrictions that are placed upon user written SQL/CLR - there are a lot of rules to bear in mind and locks is an important area.

Also be very careful in two areas:

1. Query cancellation - the manner in which SQL/CLR responds to this is undocumented (at least up to SQL Server 2008) and if you do not know this can hurt you later. Cancelling a running (managed) query (like in SSMS) at a point in time when the thread is running managed code AND a lock is held - will cause the AppDomain to be unloaded and thus ALL threads in that AppDomain get aborted and all users get impacted.

2. When a query is cancelled then the thread that is running managed code experiences a ThreadAbortException which can be handled if you want BUT if the thread holds a managed lock then it will NOT see that exception and the AppDomain will just be unloaded.

3. And here is the killer - Microsoft's own code (ADO.NET) sometimes gets locks (this is in violation of their own published guidelines) and so cancelling a query that is doing little more than accessing some table can also cause the entire AppDomain to be unloaded and potentially large numbers of user impacted (unless you design for this - good luck !).

Here is a bug report about this that someone submitted a year ago yet has been ignored for approx one year it seems.

There are some very serious gaps in both SQL/CLR and its documentation (and it seems the responsiveness to bug reports !) so tread carefully.

Cap'n

Answer 4

I'd like to know where you'd heard/read the escalation policy is configurable. I've not heard that.

Cheers, Bob

Answer 5

I'd like to know where you'd heard/read the escalation policy is configurable. I've not heard that.

Cheers, Bob

I cant recall - pretty sure I read it Bob or at least felt it was implied - let me think back and see if I can find anything...

Cap'n