You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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: 12/03/2009 03:54:36 - Revision: 2.0

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

  • kbnomt kbrapidpub KB960922
Feedback
">play:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">