CODEWITHSUNDEEP
nhibernateupgradeorchardcmssqlclient
DrewMadueke
DrewMadueke

Reputation: 19

Nhibernate error messages in Azure SQL Managed Instance and on prem SQL

Has anyone encountered similar issues? The exception below just started popping up out of nowhere after many years of using Orchard. The site gets locked and no one is able to navigate for periods of time. Would it help to upgrade Nhibernate to a newer version? Should I also look at upgrading orchard Framework to version 1.10.3? Moving to Orchard Core is out of question for now due to budget. Let me also mention that we're not using the Orchard.sdf. We're reading the orchard settings from databases in a Sql Managed Instance and Sql Server on Prem.

Current Orchard Framework Version: 1.10.2 Current Nhibernate version: 4.0.4.4000

Exception NHibernate.Exceptions.GenericADOException: could not execute batch command.
[SQL: SQL not available] ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.
  The timeout period elapsed prior to completion of the operation or the server is not responding.
  Operation cancelled by user.
  The statement has been terminated. ---> System.ComponentModel.Win32Exception: The wait operation timed out
     --- End of inner exception stack trace ---
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
     at System.Data.SqlClient.SqlCommandSet.ExecuteNonQuery()
     at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)
     --- End of inner exception stack trace ---
     at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)
     at NHibernate.AdoNet.AbstractBatcher.ExecuteBatchWithTiming(IDbCommand ps)
     at NHibernate.AdoNet.AbstractBatcher.ExecuteBatch()
     at NHibernate.Engine.ActionQueue.ExecuteActions()
     at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
     at NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event)
     at NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet1 querySpaces)
     at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
     at NHibernate.Impl.CriteriaImpl.List(IList results)
     at NHibernate.Impl.CriteriaImpl.ListT
     at Orchard.ContentManagement.DefaultContentQuery.Slice(Int32 skip, Int32 count)
     at Orchard.ContentManagement.DefaultContentQuery.ContentQuery1.Orchard.ContentManagement.IContentQuery<T>.List()
     at Orchard.Widgets.Services.DefaultLayerEvaluationService.PopulateActiveLayers() in D:\a\1\s\GMS_Portal\Workforce\KLP_Branch_GMS_Portal\src\Modules\Orchard.Widgets\Services\DefaultLayerEvaluationService.cs:line 45
     at Orchard.ContentManagement.Utilities.LazyField1.GetValue()
     at Orchard.ContentManagement.Utilities.LazyField1.get_Value()
     at Orchard.Widgets.Services.DefaultLayerEvaluationService.GetActiveLayerIds() in D:\a\1\s\GMS_Portal\Workforce\KLP_Branch_GMS_Portal\src\Modules\Orchard.Widgets\Services\DefaultLayerEvaluationService.cs:line 38
     at Orchard.Widgets.Filters.WidgetFilter.OnResultExecuting(ResultExecutingContext filterContext) in D:\a\1\s\GMS_Portal\Workforce\KLP_Branch_GMS_Portal\src\Modules\Orchard.Widgets\Filters\WidgetFilter.cs:line 52
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilterRecursive(IList1 filters, Int32 filterIndex, ResultExecutingContext preContext, ControllerContext controllerContext, ActionResult actionResult)
     at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext controllerContext, IList1 filters, ActionResult actionResult)
     at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass3_1.b__1(IAsyncResult asyncResult) has been thrown in controller Tax.Core.Framework.Controllers.HomeController redirecting user to '~/Login/ApplicationError'

Have tried to play with properties in the Managed SQL instance database. Setting "Is Read Committed on" to true does not seem to help the issue.

Upvotes: 1

Views: 103

Answers (1)

Robert Fricke
Robert Fricke

Reputation: 3643

I suspect the issues are with the Azure SQL Managed Instance, since we experienced the exact same issues with connection timeouts yesterday just after Microsofts Scheduled Maintenance of this resource in West Europe. Connections would work if they were taken out of the connection pool, but whenever the connection wasn't in a pool but had to be established we usually saw a connection timeout. The issue started out of the blue.

We patched the issue by adding Min Pool Size=[number] in our application connection strings, and setting [number] to a number about twice as high than previously required. After that the apps never needed to create new connections but reused ones from the pool.

Today we restarted our Azure SQL Managed Instance and things look like normal again. It might have been the restart was not required and things just look better today anyway.

Upvotes: 0

Related Questions