访问Geography数据触发OutOfMemoryException错误求助

问题现象 #

网站此前运行正常，现在访问包含Geography数据查询的页面时，抛出OutOfMemoryException，错误源于Microsoft.SqlServer.Types.SridList的静态构造函数。但在SQL Server Management Studio中可正常查询该数据；同一服务器上部署相同代码、仅数据库不同的网站运行正常，故障数据库可用空间仅6MB，正常数据库为60MB，自动增长设置均为64MB。

堆栈跟踪 #

[OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.]
    Microsoft.SqlServer.Types.SridList..cctor() +0

[TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Types.SridList' threw an exception.]
    Microsoft.SqlServer.Types.SqlGeography.set_Srid(Int32 value) +26
    Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid) +52
    lambda_method(Closure , BinaryReader ) +140
    System.Data.Entity.SqlServer.SqlSpatialDataReader.GetGeography(Int32 ordinal) +216
    System.Data.Entity.Core.Common.Internal.Materialization.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal) +232
    System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetSpatialPropertyValueWithErrorHandling(Int32 ordinal, String propertyName, String typeName, PrimitiveTypeKind spatialTypeKind) +328
    lambda_method(Closure , Shaper ) +7306
    System.Data.Entity.Core.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly(Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet) +258
    lambda_method(Closure , Shaper ) +518
    System.Data.Entity.Core.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper) +384
    System.Data.Entity.Core.Common.Internal.Materialization.RowNestedResultEnumerator.MaterializeRow() +137
    System.Data.Entity.Core.Common.Internal.Materialization.<MoveNextAsync>d__7.MoveNext() +355
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Data.Entity.Core.Common.Internal.Materialization.<TryReadToNextElementAsync>d__12.MoveNext() +360
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    System.Data.Entity.Core.Common.Internal.Materialization.<MoveNextAsync>d__a.MoveNext() +369
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    System.Data.Entity.Internal.<FirstMoveNextAsync>d__0.MoveNext() +614
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    System.Data.Entity.Infrastructure.<ForEachAsync>d__5`1.MoveNext() +379
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    Repository.Pattern.Ef6.d__f.MoveNext() +327
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    Repository.Pattern.Ef6.d__0.MoveNext() +320
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    BeYourMarket.Web.Controllers.d__af.MoveNext() +2254
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    BeYourMarket.Web.Controllers.d__9c.MoveNext() +203
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
    System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) +92
    System.Web.Mvc.Async.<>c__DisplayClass37.b__36(IAsyncResult asyncResult) +22
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +69
    System.Web.Mvc.Async.AsyncInvocationWithFilters.b__3d() +72
    System.Web.Mvc.Async.<>c__DisplayClass46.b__3f() +396
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +69
    System.Web.Mvc.Async.<>c__DisplayClass2b.b__1c() +38
    System.Web.Mvc.Async.<>c__DisplayClass21.b__1e(IAsyncResult asyncResult) +188
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +59
    System.Web.Mvc.Controller.b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +32
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +73
    System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +73
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +39
    System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +59
    System.Web.Mvc.MvcHandler.b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +46
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +73
    System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +59
    System.Web.CallHandlerExecutionStep.InvokeEndHandler(IAsyncResult ar) +231
    System.Web.CallHandlerExecutionStep.OnAsyncHandlerCompletion(IAsyncResult ar) +172

解决方案 #

• 紧急修复数据库空间不足问题：故障数据库可用空间仅剩6MB是核心诱因。虽然SSMS能执行查询，但应用层处理空间数据时，可能需要临时空间完成SridList初始化等操作，空间不足会触发内存溢出。

  1. 检查数据库所在磁盘的剩余空间，确保有足够空间支持自动增长（当前设置64MB）；
  2. 手动扩展数据库数据文件大小，或调整自动增长的增量值（如果磁盘空间允许）；
  3. 清理数据库冗余数据：删除过期历史数据、收缩日志文件、清理临时表，释放可用空间。

• 验证Microsoft.SqlServer.Types版本兼容性：对比正常运行站点的Microsoft.SqlServer.Types NuGet包版本，确保故障站点使用相同版本。版本不匹配可能导致空间数据处理时的内存异常。

• 优化Geography数据查询逻辑：即使SSMS能查询，应用层可能一次性加载了过多空间数据导致内存溢出。

  1. 对查询结果进行分页，避免一次性读取全部Geography对象；
  2. 仅返回必要的空间字段，减少数据传输量和内存占用；
  3. 考虑在数据库端对Geography数据进行简化处理（如降低精度）后再返回给应用。

• 检查应用服务器内存配置：确认应用池的内存限制设置，避免因应用池内存配额不足导致溢出。对比正常站点的应用池配置，保持一致。

内容的提问来源于stack exchange，提问作者Claudia Goga