The CLR code we are using is very simple:
IsInvariantToDuplicates:=True, IsInvariantToNulls:=True, IsInvariantToOrder:=True)> _
Public Structure BitMaskOR
Dim val As SqlInt32
Public Sub Init()
val = 0
Public Sub Accumulate(ByVal value As SqlInt32)
val = val Or value
Public Sub Merge(ByVal value As BitMaskOR)
val = val Or value.val
Public Function Terminate() As SqlInt32
The code shouldn't be long running - most queries should complete within a few seconds. Also, I wouldn't have thought the memory requirements for this CLR function would be huge. Do you think a thread.sleep statement would make a difference?
Also, if this problem happens again what is the best way to handle it? The stored procedures were failing with the ".NET Framework execution was aborted by escalation policy because of out of memory. ". I tried clearing the cache, but this didn't help. Eventually we decided to restart the SQL Server service - this fixed the problem, but it's not an ideal solution.