none
After working for a while an SQLCLR assembly wont load RRS feed

  • Question

  • I have a user defined aggregate function in an assembly called Watchdog.
    I use that function in a stored procedure.
    All the sudden after running just fine for a day it throws an error as soon as I try to execute the stored procedure with this error:

    Msg 10314, Level 16, State 12, Procedure spwdAutomaticAction, Line 70
    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 
    System.IO.FileNotFoundException: Could not load file or assembly 'watchdog, Version=0.0.0.0, Culture=neutral, PublicKeyToken=fffbd94e193b6c65' or one of its dependencies. The system cannot find the file specified.
    System.IO.FileNotFoundException: 
       at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
       at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
       at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
       at System.Reflection.Assembly.Load(String assemblyString)
    

    I have verified that the Watchdog assembly still has PERMISSION_SET = UNSAFE and that the only assembly it references also has it. Why would it say FileNotFoundException?

    Thursday, October 3, 2013 11:49 AM

Answers

  • Ok, it's apparently a bug in SQL Server.
    If you somehow end up with an assembly with a blank clr_name you can't drop it unless you manually edit the system base tables which isn't supported and can fuck up the database.

    Best solution seems to be to create a new assembly with a new name and remove any reference to the original one and leave it.

    • Marked as answer by Hans E. Molin Thursday, August 7, 2014 12:49 PM
    Thursday, August 7, 2014 12:49 PM

All replies

  • Have you tried dropping and recreating the assembly and/or bringing SQL Server down and back up to see if the assembly really is out of resources (e.g. memory)? What version of SQL Server and what "bitness" are you running?

    You can also monitor the Assembly:Load event with SQL Profiler to see if it is an assembly (or dependent assembly) load issue.

    Cheers, Bob

    Thursday, October 3, 2013 2:08 PM
    Moderator
  • I tried dropping the assembly (after dropping the objects depending on the assembly) and I got:

    Msg 701, Level 17, State 13, Line 1
    There is insufficient system memory in resource pool 'default' to run this query.

    Not sure why it would require any substantial amount to drop an assembly.

    The trace of Assembly:Load gave me nothing, meaning no such event occurred.

    Thursday, October 3, 2013 2:30 PM
  • Checking the SQL Server logs I found this:

    10/02/2013 05:38:16,spid71,Unknown,AppDomain 8 (Watchdog.dbo[runtime].7) created.
    10/02/2013 05:36:02,spid16s,Unknown,AppDomain 4 (Watchdog.dbo[runtime].3) unloaded.
    10/02/2013 05:36:02,spid1s,Unknown,AppDomain 4 (Watchdog.dbo[runtime].3) is marked for unload due to memory pressure.

    05:38 was the first occurance of the error when trying to use the sp.

    Thursday, October 3, 2013 3:04 PM
  • It matters what version of SQL Server you're on (which is why I asked).

    In 32-bit pre-SQL 2012 - SQLCLR memory is allocated from mem_to_reserve. You change this with the -g startup switch.

    In 64-bit pre-SQL 2012 - SQLCLR memory is allocated out of what's left over that SQL Server doesn't use. You must adjust max_server_memory downward to give SQLCLR more memory.

    In SQL 2012 and above - SQLCLR memory is (mostly) allocated out of the buffer pool, max_server_memory should cover it. I'd suspect a different problem.

    Sounds like either memory is being fragmented (due to large SQLCLR memory allocations) over time or leaked. Check that your assembly code doesn't leak memory. If DROP doesn't work, sounds like you are running really low on memory. You can also observe SQLCLR GCs (garbage collections, in the CLR-specific performance monitor counters (in the SQL Server process) to see if it's doing a lot of them (i.e. it's running under memory pressure).

    Hope this helps, Cheers, Bob

    Thursday, October 3, 2013 8:14 PM
    Moderator
  • It's a SQL Server 2008 R2 64-bit on Windows Server 2008 R2.

    One problem is that no assembly is currently loaded,
    select * from sys.dm_clr_loaded_assemblies
    returns nothing.

    I can't restart it right now since it's a live system. But increasing max memory during runtime didn't seem to change anything.

    If I understand correctly SQL CLR uses VAS Reservation, so just increasing total memory would not help that. But I don't have any assemblies loaded, so it would be nice to somehow list what is using memory from VAS Reservation since it's not my assembly at the moment at least.

    I also tried lowering the max memory to free up memory to VAS Reservation but that didn't help either.

    Friday, October 4, 2013 8:03 AM
  • If SQL Server is the only process (outside of OS) running on the box, and it's 2008 R2 64-bit, you should be able to free up memory for SQLCLR (and other things like OLE DB linked servers that use the multipage memory allocator) by lowering max_server_memory with sp_configure.

    Don't forget to follow that with a "reconfigure" or "reconfigure with override" statement too. You can see how much memory SQL Server is using by querying SQL Server: Memory Manager counters in perfmon. To see how much is  used for SQLCLR I believe that would be the SQLServer:CLR counter. You can also get an overview of what SQL Server is using for memory with DBCC MEMORYSTATUS. There's also a bunch of sys.dm_os* DMVs that report on memory listed here: http://technet.microsoft.com/en-us/library/ms176083(v=sql.100).aspx note that SQLCLR memory (in SQL Server 2008 R2) mostly uses the multipage memory allocator. In the sys.dm_os_memory_objects DMV you can use "where type like '%CLR%' to find memory allocated by SQL Server for SQLCLR.

    If SQL Server isn't the only process running outside the box, other processes (like SSIS or SQL Agent jobs) can deprive SQLCLR of memory also, because it's just using memory outside mem_to_reserve in pre-2012 64-bit.

    It sounds like (by your error messages here):

    10/02/2013 05:38:16,spid71,Unknown,AppDomain 8 (Watchdog.dbo[runtime].7) created.
    10/02/2013 05:36:02,spid16s,Unknown,AppDomain 4 (Watchdog.dbo[runtime].3) unloaded.
    10/02/2013 05:36:02,spid1s,Unknown,AppDomain 4 (Watchdog.dbo[runtime].3) is marked for unload due to memory pressure.

    that when SQLCLR tries to load your assembly (and all of its dependent assemblies) it's taking some large initial memory allocation that there isn't enough available memory for SQLCLR to handle. It may be that the available memory is too fragmented too obtain the allocation; at that point your only choice is to recycle SQL Server (and/or possibly the OS).

    Also, check to see if you have all of the dependent assemblies cataloged (in sys.assemblies) just in case, because the error message is "file not found".

    Hope this helps, Bob

    Saturday, October 5, 2013 5:45 PM
    Moderator
  • I get the exact same error on another server where it's never even loaded the assembly so memory does not seem to be the issue.

    I think there's something wrong with a referenced assembly. 
    If I try to update it with alter assembly it says

    Assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.
    But System.Drawing is there and with the version 2.0.0.0.

    Restarting the server doesn't help.

    If I try to update System.Drawing it just says it's the same as the one already loaded.
    Got another server where it works just fine, same OS, same SQL.
    Tuesday, December 3, 2013 4:02 PM
  • If you're using references to unsafe assemblies (like System.Drawing), the dependencies must be recataloged every time the MVID changes. Simply looking at the version number in the error message is not enough (there's never, to my knowledge, been a System.Drawing.dll, who's actual 4-part version number is 2.0.0.0, if you right-click on the assembly itself and look at the file version in the details tab). I'd try dropping and recataloging all dependent assemblies, or at least any dependent assemblies that produce that particular error.

    Cheers, Bob

    • Proposed as answer by DeviantLogic Tuesday, April 29, 2014 11:48 AM
    • Unproposed as answer by Hans E. Molin Wednesday, July 2, 2014 11:37 AM
    Tuesday, December 3, 2013 4:30 PM
    Moderator
  • Ok, it's apparently a bug in SQL Server.
    If you somehow end up with an assembly with a blank clr_name you can't drop it unless you manually edit the system base tables which isn't supported and can fuck up the database.

    Best solution seems to be to create a new assembly with a new name and remove any reference to the original one and leave it.

    • Marked as answer by Hans E. Molin Thursday, August 7, 2014 12:49 PM
    Thursday, August 7, 2014 12:49 PM