Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset?

Question

I want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?

Answer 1

There is a more elegant solution that won't affect all other assemblies: just change the PERMISSION_SET of one of the assemblies in the app domain (app domains are per user).

ALTER ASSEMBLY [AssemblyName] WITH PERMISSION_SET = {1 of the 2 levels that 
                                                      this assembly is not current at}

Just remember that you will need to set the PERMISSION_SET back to what it was. Also, you need to access a method in the assembly before changing the PERMISSION_SET will unload it; changing an assembly that is not currently loaded into an app domain that is active, but with another assembly, has no effect on the app domain (App Domains are per-DB, per-User, not per-Assembly).

---

UPDATE
The method described above is the most fine-grained approach where it will only unload that one App Domain. But, it does require that the assembly can be set to one of the other two levels. For assemblies marked as SAFE it will only be possible if either

• the database is set to TRUSTWORTHY ON, or
• the assembly is signed and a Login, based on an asymmetric key that is itself based on the same signature as the assembly, exists and has been granted either the EXTERNAL ACCESS ASSEMBLY or the UNSAFE ASSEMBLY permission

In this case you can simply turn the TRUSTWORTHY setting ON and then immediately back OFF again and that will unload all App Domains in that particular database:

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;

If you only have one App Domain in the database anyway (and I suspect this is the case 95%, or more, of the time), then both of the methods described here have the same net effect. And in that situation, the ALTER DATABASE method seems simpler as it doesn't require specifying a particular object name nor does it require knowing what the original PERMISSION_SET was.

ALSO, if you only have a single App Domain then the ALTER DATABASE method is simpler even in the case where the database is either already set to TRUSTWORTHY ON or you have set up the key-base login with the appropriate permission. If you are using a key-based login then you can set TRUSTWORTHY to ON and then OFF again as mentioned above. But if you already have TRUSTWORTHY set to ON, then just reverse it and set it to OFF and then immediately back to ON:

ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;
ALTER DATABASE CURRENT SET TRUSTWORTHY ON;

Answer 2

I know this is a bit brutal, but what about disabling the CLR and re-enabling it?

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO