DDL appdomains appear in SQL Server log in SQL Server 2008

.NET (and therefore SQLCLR) divides up running its code (even within the same process like the sqlserver.exe process) into appdomains. The appdomain is like a lightweight process used to enforce isolation between running .NET code within the same Windows process. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate execution of .NET code on a per database and per assembly owner basis.

SQLCLR uses appdomains for two reasons: for running .NET user code like functions and procedures, and for running DDL to create and alter assemblies. You can see appdomains being created and destroyed in the SQL Server log, as well as query the current appdomains by using the sys.dm_clr_appdomains dynamic management view.

In SQL Server 2005, the SQL Server log only showed user code-running appdomains, the messages look like this:

AppDomain 4 (testdb.dbo[runtime].3) created.
AppDomain 4 (testdb.dbo[runtime].3) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 4 (testdb.dbo[runtime].3) unloaded.

Is these messages there's a appdomain being created for running code in the testdb database for assemblies owned by dbo. It's a runtime appdomain. The last two messages show the appdomain (some time later) being unloaded.

In SQL Server 2005 it was difficult to "see" DDL appdomains, because their presence was not logged in the SQL Server log. You could deduce they existed by noting that the AppDomain numbers (e.g. AppDomain 4 above) seemed to contain skips in the number range. Or if you were extremely lucky, by querying sys.dm_clr_appdomains at *exactly* the right time.  In SQL Server 2008, it is. You don't see them being created, but I just executed some DDL to create a SQLCLR assembly and SQL Server log reports:

AppDomain 5 (testdb.dbo[ddl].4) unloaded.

This should make it a little easier to diagnose appdomain-related problems or see when assemblies have been created or altered.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.