When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down immediately after they are used. A friend of mine recently wanted to shutdown an appdomain on purpose to troubleshoot a problem that he thought might have been SQLCLR-related. So how do you shutdown a runtime appdomain on purpose?

You could write a .NET proc to call AppDomain.Unload. But I shied away from this for a few reasons. You'd need to catalog the appdomain as unsafe for the proc to work, which means marking database as trustworthy or doing the 'signed assembly with key in master' dance. And I'd really prefer a way to have SQL Server gracefully shutdown the appdomain itself.

SQL Server will shutdown an appdomain for different reasons. It can shut them down under extremely low memory conditions or when there is a serious enough unhandled exceptional condition (e.g. unhandled exceptional condition that could leave .NET locks in place). We really don't want to cause either of these on purpose just to shut down an appdomain. Another reason the SQL Server will shut down an appdomain is when a loaded assembly is altered. You can use the ALTER ASSEMBLY DDL statement to replace code in place, subject to limitations. When you alter an assembly in place SQL Server recycles the appdomain to be able to use your new code. Currently executing code will continue to use the appdomain until the call completes; new requests are routed to the new appdomain (with the updated code). When all current requests against the old appdomain complete, the appdomain shuts down. Hmmm, probably not a good idea to muck with recompiling the producting code either. So…

Compile a simple do-nothing assembly with a simple do-nothing function (say, add two numbers together). We'll call the assembly 'fred' and the function 'addtwo'. The assembly must be owned by the same owner as the appdomain you want to recycle (remember runtime appdomains are on a per database and assembly owner basis). So if the appdomain we want to recycle is the 'pubs.dbo[runtime]' appdomain…

use pubs
go

create assembly fred authorization dbo …
create function dbo.addtwo …
use the function dbo.addtwo (this causes the assembly to be loaded)
recompile the assembly fred
alter assembly fred … (this cause the eventual appdomain unload)

Note that you don't have to change the 'fred' assembly, only recompile it. SQL Server decides that an assembly is changed if it has a different MVID (.NET assembly module version identifier). New MVIDs are assigned each time an assembly is recompiled (note that an MVID is not the same as a four-part assembly version number). In Visual Studio, you'd recompile the assembly by using the Recompile menu entry, not the Build menu entry. If you try and run alter assembly without a rebuild you'll get the error message 'ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "fred"'. You could conceivably use Visual Studio autodeploy for these steps as well, but Visual Studio autodeploy drops the functions and assembly and recreates them rather than using ALTER ASSEMBLY.

The next request against any of the .NET database objects will cause a new appdomain to be created.