Problem: ArcGIS clients encounter errors related to table schema changes, app domain unloading, or fetch buffers when working with SQL Server native spatial types

Description

When editing or displaying spatial data stored using SQL Server native spatial types, ArcGIS client applications may encounter the following errors:

Error:
Could not complete cursor operation because the table schema changed after the cursor was declared.
 
Error:
The app domain with specified version id (%d) was unloaded due to memory pressure and could not be found.
 
Error:
[Microsoft][SQL Server Native Client 11.0][SQL Server]There are no rows in the current fetch buffer.

In the SQL Server error logs, the error is usually related to memory pressure forcing the Common Language Runtime (CLR) application domain to unload.

Note:
By default, the error log is found in the following folder location: 
C:\Program Files\Microsoft SQL Server\MSSQL##.MSSQLSERVER\MSSQL\Log

Cause

Microsoft SQL Server spatial types are heavily dependent on the CLR. When the application domain hosting the CLR data is unloaded, this is treated as a schema change to the database engine and may result in errors while displaying and editing feature classes stored using either the geometry or geography types. 

As the errors indicate, this is a memory issue within SQL Server that forces the CLR application domain hosting the spatial type to unload.

Solution or Workaround

In earlier versions of SQL Server (SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2) the limits of physical memory that the buffer pool consumes are determined by the max server memory (MB) and min server memory (MB) configuration options. CLR allocations, including the SQL CLR heaps and its global allocations that are created during CLR initialization, are not determined by these configuration options.

Starting with SQL Server 2012, single-page, multi-page, and CLR allocations are consolidated into an "any size" page allocator, and it is included in memory limits controlled by max server memory (MB) and min server memory (MB) configuration options. This change provides a more accurate sizing ability for all memory requirements that go through the SQL Server Memory Manager. If SQL Server Memory Manager notices a low memory situation, it can start unloading pages from memory. CLR allocations are considered a lower priority and are therefore more likely to be unloaded.

This is valuable information when deciding how much memory must be allocated using the max server memory (MB) and min server memory (MB) configuration options. By default, the max server memory is 2147483647 MB, or all of the available memory on the server. It is important to set the max server memory configuration option to a value that restricts SQL Server memory consumption to allow other processes like the operating system to have access to adequate memory. Although seemingly counter-intuitive, reducing the SQL Server memory pool allows other processes or applications to consume the memory needed without impacting the memory allocated to SQL Server. If all memory on the server is allocated to SQL Server, memory pressure errors are more likely to occur.

• For SQL Server 2005, 2008, and 2008 R2 releases, incorrectly setting the max server memory too high may allow the memory manager to see and control too much of the total server memory to be used by the buffer pool. CLR allocations would be made from the remaining available memory, which could leave too little memory for the CLR. 
• For SQL Server 2012 or higher releases the memory management strategy may be different. Since CLR memory allocations are now controlled by the server memory configuration options, it may be possible to set the max server memory higher than in previous releases of SQL Server. 
• Starting with SQL Server 2016, it invokes the native implementation of several spatial methods which has improved performance and further reduced CLR-based memory pressure errors.

Consider whether there are other applications or processes running on the system besides SQL Server, and how much memory is actually available on the server. It is possible that there is not enough memory installed on the server to handle SQL Server operations along with those required by the operating system. Other applications may be using physical memory that should be available to SQL Server. It is generally advised that SQL Server be on its own dedicated server. Other applications that compete for memory, such as ArcGIS, should be installed on different machines.

Microsoft indicates there is ongoing work to fix more general CLR out-of-memory issues. If any of the aforementioned errors are encountered while using ArcGIS, or are found in the SQL Server error logs, it is recommended to call Microsoft Support to further investigate the root cause of the error.

When calling Microsoft Support about this issue, provide Incident #114080611682006 and Defect #3374271 to the support analyst.
 

Knowledge Base Note:
This article was previously documented as KB 43036.