1

I have the following query:

Insert into PropertyTractsTemp
    Select 
        P.field1 as field1 , 
        P.field2 as field2, 
        P.field3 as field3, 
        P.field4 as field4, 
        G.GEOID, 
        P.field5 as field5
    from PROPERTY_PARAMETERS P
    join BH_EDW.dbo.REF_GEOID_GEOG G WITH(INDEX([geog_sidx]))on G.geom.STIntersects(P.GeoLocation) =1
    where P.GEOID is null

This may have 1,000 to 100Mil records to match up. Either way, the query stops and I receive this error:

Msg 10316, Level 16, State 1, Line 3
The app domain with specified version id (4) was unloaded due to memory pressure and could not be found.
The statement has been terminated.

I'm told it's a CLR error, and it has something to do with the spatial index but no explanation on how to troubleshoot it.

Any wisdom would be greatly appreciated.

arcee123
  • 101
  • 9
  • 41
  • 118
  • Are you using 32 bit SQL Server? – hcaelxxam Sep 08 '16 at 15:30
  • 64-bit Web Edition. This query used to work before. Thanks. – arcee123 Sep 08 '16 at 15:48
  • Ah, your error is similar to an issue that occurs with 32 bit, but the root cause does not exist in 64 bit. I don't think I have any insight for you; sorry. – hcaelxxam Sep 08 '16 at 15:56
  • what was the 32-bit solution? – arcee123 Sep 08 '16 at 16:05
  • In 32 bit there is a memory allocation specifically for CLR functions that is set fairly small by default, causing you to get memory errors; so you have to increase that reservation by using a -g parameter on startup. That being said, the reservation is not handled the same way in 64 bit, so that shouldn't be your issue. – hcaelxxam Sep 08 '16 at 16:13

1 Answers1

0

i had same problem using a custom Clr type in a column. i solved adding "-g1024" (or you can choose an other value) on SqlServer service. enter image description here

elle0087
  • 840
  • 9
  • 23
  • no sorry, i still have the prob...was solved only after the first sql service, but after some queries, the problem remains... – elle0087 Jul 07 '17 at 13:43