Kentico Upgrade Error

Hello Kentico Team,

Recently we are trying to upgrade our multi site Kentico application and database from version 9 to version 12. Within the process of upgrade we are experiencing some issues

Before start with issues required information about the project:-

1. Current Kentico Version: - 9.0.50
2. Custom coding - None (Note a single line custom code implemented. All site using portal engine)
3. Project type - Web Application (CMSApp.sln)
4. Requirement - Upgrade to Kentico 10.0.0 (later 11, 12 accordingly)

We are doing the upgrade with steps mentioned in Upgrading to Kentico 10 documentation. As we do not have any custom coding we are skipping the Custom code analysis and Upgrading custom code step. Every steps with default options (not using Switch to advanced mode) going without any issues with Kentico Upgrade Utility tool except the last step. On last step we are getting an error message as shown in below screenshot

We opened the log file to understand the issue in detail. The error part of the log.txt is below

SQL script applied.
90% of SQL script applied.
80% of SQL script applied.
70% of SQL script applied.
60% of SQL script applied.
50% of SQL script applied.
40% of SQL script applied.
30% of SQL script applied.
20% of SQL script applied.
10% of SQL script applied.
IF NOT EXISTS
(
    SELECT 1
    FROM [CMS_SettingsKey]
    WHERE KeyName IN ('CMSDataVersion', 'CMSDBVersion')
    HAVING MAX(KeyValue) = MIN(KeyValue)
)
BEGIN
    RAISERROR('The database has been upgraded from a previous version, but the process is incomplete. Before applying further upgrades, you need to open the related site in a browser and wait until the first request finishes.', 18, 1)
END

Failed to run SQLscript: 
[DataConnection.HandleError]: 

Query: 
IF NOT EXISTS
(
    SELECT 1
    FROM [CMS_SettingsKey]
    WHERE KeyName IN ('CMSDataVersion', 'CMSDBVersion')
    HAVING MAX(KeyValue) = MIN(KeyValue)
)
BEGIN
    RAISERROR('The database has been upgraded from a previous version, but the process is incomplete. Before applying further upgrades, you need to open the related site in a browser and wait until the first request finishes.', 18, 1)
END


Caused exception: 
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Message: 
[DataConnection.HandleError]: 

Query: 
IF NOT EXISTS
(
    SELECT 1
    FROM [CMS_SettingsKey]
    WHERE KeyName IN ('CMSDataVersion', 'CMSDBVersion')
    HAVING MAX(KeyValue) = MIN(KeyValue)
)
BEGIN
    RAISERROR('The database has been upgraded from a previous version, but the process is incomplete. Before applying further upgrades, you need to open the related site in a browser and wait until the first request finishes.', 18, 1)
END


Caused exception: 
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Stack Trace: 
   at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex)
   at CMS.DataEngine.AbstractDataConnection.ExecuteNonQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)
   at Hotfix.Installer.HotfixHelper.RunSQLCommand(String text, IDataConnection connection)
   at Hotfix.Installer.HotfixHelper.RunSQLScript(String fileName, String connString, String defaultUICulture, IMessageLog messageLog)
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
   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.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, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at CMS.DataEngine.AbstractDataConnection.ExecuteNonQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)
The semaphore timeout period has expired


Running SQL script (upgrade.sql)
DLLs\Net45\App
DLLs\Net45\Azure
DLLs\Net45\Azure

We would like to know why we are getting the above mentioned issue with default Kentico setup. From the log file content it is clear that the database has been upgraded successfully but the whole process isn't complete yet. As per documentation information

• On web application projects, the upgrade utility rebuilds the solution at the end of the upgrade by default. ---- Maybe our upgrade process occurring the error before starting this process.
• If your project contains custom code that is no longer supported in Kentico 10, or has certain modules uninstalled, the rebuild will not be successful. ---- There is no custom code implemented within this application.
• You can disable the Rebuild web project option on customized projects, and perform the rebuild manually once you update the project after the upgrade." ---- We haven't disabled the Rebuild option as this is not customized project. However, the manually rebuild process completing successfully.

Acceptance :-

As we have told you that we can rebuild the Web Application manually without any issues and as per documentation we are opening the website to verify and making it ready for next upgrade. Interestingly the website loads correctly finally. After this we again upgraded it to Version 11.0.0 but even this time the same issue occurred at the last step on Kentico Upgrade Utility.

For each version, you always need to open the upgraded website in a browser before starting the upgrade for the next version and verify that the site loads correctly. During the first request after the upgrade, the system performs certain tasks required to finish the upgrade, including the import of new objects.

Question and concerns :-

1. Why this issue is arising when we are using default upgrade procedure ?
2. How can we understand if the upgrade is successful and complete ? (To us merely the site is running finally not acceptable as some module upgrade can be incomplete)
3. Withing error list "The semaphore timeout period has expired" is also exists. If this is the case then how much time limit for SQL Server will be sufficient for this process ?
4. With the current upgrade scenario how to make sure the current running application is guaranteed future upgrades and hotfixes compatible ?