11

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

1
  • Not sure if you get notifications on answer updates, but I updated my answer with an even easier method :). Feb 6, 2015 at 20:09

2 Answers 2

6

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
4
  • 2
    One important detail about this method is that it works when executed against a STANDBY (read-only) database; all other methods I've tried do not. I needed this because an update to a CLR assembly propagated as normal via log shipping to a STANDBY catalog, but the AppDomain didn't reload---so it continued running the code from the old version of the .dll for about a day.
    – Granger
    Oct 28, 2014 at 16:19
  • @Granger very interesting and good to know :). However, I would consider that a bug within SQL Server. You might want to report that via the Connect site: connect.microsoft.com/SQLServer/Feedback Oct 29, 2014 at 17:32
  • 1
    @srutzky - Thanks for the suggestion; I expect they'd just close the report as "Won't fix". The setting is server-wide, not per-catalog (just like 'nested triggers', 'filestream access level', etc.). That's quite the can of worms I'd be trying to open.
    – Granger
    Oct 30, 2014 at 15:02
  • @Granger (and Max): I was not clear about what I was saying I thought was a bug. I was not saying that resetting the "CLR Enabled" setting causing the unload was a bug. I was saying that the ALTER ASSEMBLY propagated via log shipping that didn't reload (or at least unload) the App Domain was the bug. Either way, I found an even easier method that I added to my answer here. If you had the ability to test this new method that would be great as I am very curious to see if it works in the log shipping scenario you described. Feb 6, 2015 at 15:46
8

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;
1
  • 1
    The updated approach works on a STANDBY (READ_ONLY) database catalog. Sql Server allowed me to change the "TRUSTWORTHY" setting, then restore it to what it was before. I verified that the change actually unloaded the domain by looking at the result from SELECT * FROM sys.dm_clr_appdomains;. Sweet.
    – Granger
    Feb 10, 2015 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.