Mere Query timeout can lead to fullscale SQLCLR AppDomain unload in some conditions.

Article ID: 960922 - View products that this article applies to.
Expand all | Collapse all
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

Within SQLCLR context, a user-coded .NET object that connects to its host instance using the special “Context Connection”  (which internally calls SqlInternalConnection) will momentarily take a .NET lock on a SqlInternalConnectionSmi object. If a client time-out happens for the SPID that initiated the Context Connection when the lock is held, it will generate a full .NET Appdomain upload. All the .NET activity taking place inside this AppDomain will therefore be interrupted.

To check the number of AppDomains loaded on your SQL Server, please refer to the sys.dm_clr_appdomains System View and its documentation.  

This scenario can be broadened to any .NET mechanism that takes synchblock locks and which get interrupted by an exception, likely ThreadAbort (TA).

Resolution

This behavior is by design.

More Information

Many .NET classes rely on a .NET SynchBlock mechanism to enforce thread synchronization. In order to avoid shared structures’ corruptions or ressources leaks within the SQL Server process space, the decision has been made to unload the whole AppDomain when any .NET thread owning such a lock hits a ThreadAbortException. This will clean the shared context. In our scenario, the ThreadAbortException is the expected server-side consequence of a client time-out.

This decision was made because the consistency and reliability of the whole SQL Server process are more important than failing a particular subset of .NET activities.

 

Because of the time-dependant aspect of the scenario, it is a rare issue that will happen unsystematically during high SQLCLR load.

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.

Properties

Article ID: 960922 - Last Review: December 3, 2009 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Service Pack 2
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbnomt kbrapidpub KB960922

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