Quartz jobs exhausting connection pools after episerver 8 upgrade

Vote:
 

We are in the process of upgrading to episerver 8. We have noticed an odd behavior with the Quartz jobs. ecf_Search_PurchaseOrder* stored procedue calls hit a deadlock and eventually DB connection pool is exhausted paralyzing the database all together. We have never seen this behavior before.

Are there any best practices to avoid this from happening? Please advise.

#131542
Jul 29, 2015 22:11
Vote:
 

Hi,

Which is exactly version you're on and which stored procedure causes the deadlock (we have ecf_Search_PurchaseOrder.sql, ecf_Search_PurchaseOrder_Customer.sql and 
ecf_Search_PurchaseOrder_CustomerAndName.sql)

Regards.

/Q

#131549
Jul 30, 2015 7:03
Vote:
 

It is happening on your stored procedures as well on some custom ones we created for PO searching. It does not happen to any specific jobs, also the behavior we are observing is that after a deadlock, there are subsequent deadlocks until the connection pool is out of connections. So along with correcting the deadlock, I was thinking to put in place some mechanism to close the connections (if that is possible at all)

We are in the process of upgrading to version 8.13.2.721

Here are a few sample exceptoin.

Message:[Keepsake order update abnormally terminated] Stack-Trace=[System.Exception: Exception in ecf_Search_PurchaseOrder:  ---> System.Data.SqlClient.SqlException: Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.SqlDataReader.TryHasMoreRows(Boolean& moreRows)     at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)     at System.Data.SqlClient.SqlDataReader.Read()     at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)     at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)     at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)     at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)     at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)     at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     --- End of inner exception stack trace ---     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     at Mediachase.Commerce.Orders.PurchaseOrder.Search(OrderSearch search, Int32& totalRecords)     at Mediachase.Commerce.Orders.OrderContext.GetPurchaseOrderById(Int32 orderGroupId)     at TelefloraQuartzJobs.Jobs.LawsonToMarketOrderUpdates.KeepsakeOrdersUpdateJob.ProcessApplication(String applicationName) in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\TelefloraQuartzJobs\Jobs\LawsonToMarketOrderUpdates\KeepsakeOrdersUpdate.cs:line 156]
Message:[ShopInfoAndCreditLimitsSyncJob failed processing shop 41049200.] Stack-Trace=[System.Exception: Exception in ecf_Search_PurchaseOrder_AllSuspendedLineItemsCutoff:  ---> System.Data.SqlClient.SqlException: The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following:  the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host     --- End of inner exception stack trace ---     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)     at System.Data.SqlClient.SqlConnection.Open()     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     --- End of inner exception stack trace ---     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     at dxBase.Library.Orders.TfPurchaseOrder.GetAllSuspendedOrdersCutoff(Guid customerId) in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\dxBase.Library\Orders\TfPurchaseOrder.cs:line 400     at dxBase.Library.Orders.TfOrderContext.GetAllSuspendedLineItemsforShopCutoff(Guid customerId) in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\dxBase.Library\Orders\TfOrderContext.cs:line 423     at TelefloraQuartzJobs.Jobs.ShopInfoAndCreditLimitsSync.ShopInfoAndCreditLimitsSyncJob.ProcessApplication(String applicationName) in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\TelefloraQuartzJobs\Jobs\ShopInfoAndCreditLimitsSync\ShopInfoAndCreditLimitsSyncJob.cs:line 209]
Message:[Error occured while executing AutoConfirm Orders Job for Supplies.] Stack-Trace=[System.Exception: Exception in ecf_Search_PurchaseOrder_LineItemsCutoff_Today:  ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host     --- End of inner exception stack trace ---     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)     at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)     at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)     at System.Data.SqlClient.SqlConnection.Open()     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     --- End of inner exception stack trace ---     at Mediachase.Data.Provider.SqlDataProvider.LoadDataSet(DataCommand command)     at dxBase.Library.Orders.TfPurchaseOrder.GetLineItemsCutoffToday() in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\dxBase.Library\Orders\TfPurchaseOrder.cs:line 504     at dxBase.Library.Orders.TfOrderContext.GetLineItemsCutoffToday() in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\dxBase.Library\Orders\TfOrderContext.cs:line 463     at TelefloraQuartzJobs.Jobs.AutoConfirmOrders.AutoConfirmOrdersJob.SuppliesAutoConfirm() in c:\SRC2013\TheMarket\TheMarket\DEV-Epi8\Main\TelefloraQuartzJobs\Jobs\AutoConfirmOrders\AutoConfirmOrdersJob.cs:line 155]




#131599
Jul 30, 2015 16:07
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.