Just upgraded from 4.0 to 4.1. When I try to re-index the DB through the GUI (in the maintenance area) I get the error. Site is hosted in Azure as a web app.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (258): The wait operation timed out
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable`1 parameters)
at Nop.Data.NopObjectContext.ExecuteSqlCommand(RawSqlString sql, Boolean doNotEnsureTransaction, Nullable`1 timeout, Object[] parameters) in E:\nopCommerce\sources\src\Libraries\Nop.Data\NopObjectContext.cs:line 143
at Nop.Services.Common.MaintenanceService.ReIndexTables() in E:\nopCommerce\sources\src\Libraries\Nop.Services\Common\MaintenanceService.cs:line 203
at Nop.Web.Areas.Admin.Controllers.CommonController.ReIndexTables(MaintenanceModel model) in E:\nopCommerce\sources\src\Presentation\Nop.Web\Areas\Admin\Controllers\CommonController.cs:line 229
ClientConnectionId:8f5ef3f4-8f0c-4cb4-a6ef-43651fd81575
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:cf4dae22-baa0-4093-835c-8543479bf5db
Routing Destination:d66d01d05f2e.tr30.centralus1-a.worker.database.windows.net,11222
nop 4.1 can't re-index DB through GUI
- 109
- 805
- 8/8/2011
- Canada
I would recommend the following. It provides instructions on how to rebuild indexes in a SQL Azure Environment.
https://www.beyondtrust.com/docs/privilege-management/reporting/install/db-maintenance/rebuild-indexes-sql-azure.htm
In particular the, "Rebuild All Database Indexes".
You have to create a stored procedure in SSMS. I followed the steps from here:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15
I followed the stops up to step 3 (skipping steps 4 to 7) as after that step 3, I pasted the entire query from the following link into the new stored procedure.
https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt
I skipped step 12 as I ran with "EXEC AzureSQLMaintenance 'index'" which was inserted into the third row.
I was a little nervous running this as I have very little database experience. However our Nop site was plagued with query timeouts and maxing out the DTU several times a day. When I ran a query to see how fragmented the indexes were, many were > 99% with average page space being small some less than 4%.
After making a copy of the database (just in case). I set up the stored procedure and ran it. It ran for approx. 90 minutes with DTU at 100%. Once complete, I performed some tests to be sure everything was working and now the site is much faster with no query timeouts and DTU hasn't been higher then 27%.
https://www.beyondtrust.com/docs/privilege-management/reporting/install/db-maintenance/rebuild-indexes-sql-azure.htm
In particular the, "Rebuild All Database Indexes".
You have to create a stored procedure in SSMS. I followed the steps from here:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15
I followed the stops up to step 3 (skipping steps 4 to 7) as after that step 3, I pasted the entire query from the following link into the new stored procedure.
https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt
I skipped step 12 as I ran with "EXEC AzureSQLMaintenance 'index'" which was inserted into the third row.
I was a little nervous running this as I have very little database experience. However our Nop site was plagued with query timeouts and maxing out the DTU several times a day. When I ran a query to see how fragmented the indexes were, many were > 99% with average page space being small some less than 4%.
After making a copy of the database (just in case). I set up the stored procedure and ran it. It ran for approx. 90 minutes with DTU at 100%. Once complete, I performed some tests to be sure everything was working and now the site is much faster with no query timeouts and DTU hasn't been higher then 27%.