Summary: This article discusses using TF902 as a quick workaround to bring up your SQL Server service if it fails to be started due to post upgrade script failure after installing SQL Server patch. Further troubleshooting steps to address the post upgrade script failure have been discussed in later part of this article.
Please be noted that it is not suggested to keep your SQL Server service in this state for long since your SQL Server is in an incomplete patch installation status and considered instable for your production environment.This is because part of your SQL DLL files, MSP files or related registry key record might have already been upgraded while a few other files or metadata are not upgraded.
Why We Need To Run Post Upgrade Script After SQL Patch?
------------------------------------------------------------------
After you have finished most part of installing the SQL patch (the scenarioengine.exe process and a few other related SQL and Windows process finished the change made to MSP file, registry key, and DLL files), SQL Server service (sqlservr.exe) still needs to perform another step - to run a few post upgrade TSQL scripts - to successfully complete the change made by patch installation. This is because the patch installation process mainly makes change to the related MSP files, registry key, and DLL files. We still need to apply a few remaining changes made in DLL into SQL database level by running a few system TSQL scripts. Major part of these TSQL scripts are stored in the 'Install' folder for your SQL instance while another part of them are encrypted (i.e. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Install). If the post upgrade script cannot be successfully finished , SQL Server service cannot be started successfully after the patch as the system databases' status are not ready to be started on service startup process.
Symptom
======
After installing SQL Patch, your SQL Server service cannot be started.
SQL Server service can still generate new ERROR LOG on starting up attempt.
In the latest SQL ERROR LOG file, you found below error as 'Script level upgrade for database '**' failed' indicating that SQL Server failed to perform the script level upgrade for a specific system database and caused subsequent shut down(Below are sample testing error from my LAB when reproducing the same issue)
2021-10-14 11:27:02.98 spid7s Error: 912, Severity: 21, State: 2.
2021-10-14 11:27:02.98 spid7s Script level upgrade for database '%' failed because upgrade step '%' encountered error 3930, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2021-10-14 11:27:02.98 spid7s Error: 3417, Severity: 21, State: 3.
2021-10-14 11:27:02.99 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2021-10-14 11:27:02.99 spid7s SQL Server shutdown has been initiated
Quick Workaround To Bring Up SQL Service(Not Suggested to Keep it for Long)
=====================================================
We can add trace flag 902 to SQL Server service startup parameter to skip the script upgrade process in order to bring up your SQL Server service.
However, we suggest to resolve the failure for finishing the post upgrade script as we cannot leave SQL Server service in this state with TF902 for long. It is an incomplete upgrade state and considered unstable. This is because part of your DLL files might have already been upgraded while a few other files or metadata are not upgraded.
Further Troubleshooting Steps for Post Upgrade Script Failure
=========================================
The failure of post upgrade script can be caused by issue at database level in finishing the scripts or failure at patch installation level.
The latter indicates that the post upgrade failure is actually a consequence of an earlier exception in patch installation phase rather than the root cause of the patch installation failure. To go further, we need to investigate the actual exception that causes the subsequent failure.
Phase 1: Directly check the error and failed script steps for post upgrade script failure issue
1. As the post upgrade script will also be performed at the end of the patch installation process, you can check the latest SQL Setup Bootstrap log folder for ERRORLOG file details to see in which step and in which script do we encounter the error.
One of the common causes can be an orphaned SQL login which cannot be dropped and recreated when running a few of these post upgrade scripts.
2. If we don't have the detailed ERRORLOG that records the script upgrade failure details, we can find a down time , remove trace flag 902 from SQL startup parameter and add trace flag 3601 to SQL Server startup parameter. Then we can try to start SQL Server service to reproduce the issue. Trace flag 3601 will dump script level upgrade details to the SQL Error Log. This is a trace flag used by Microsoft for internal troubleshooting purpose. Please use it under instruction and avoid using it in your PRODUCTION environment. Alternatively, we can configure X-event on service startup to check the errors and TSQL script details when we reproduce the issue. Please be noted that part of these scripts are encrypted.
Phase 2: Check the Setup Bootstrap logs for more details of the exception on SQL Server patch installation process
If the post upgrade script is actually a consequence of failure encountered during SQL patch installation process , then the issue is not at database level but patch installation phase. We need to investigate the exception encountered on patch installation process in order to address the issue.
If this is the case, check the 'Detials.txt' and other logs on the Setup Bootstrap 'Log' folder for this issued patch installation and investigate further.(Default path: “C:\Program Files\Microsoft SQL Server\<YourSQLVersion>\Setup Bootstrap\Log\<YourIssuedPatchInstallationTime>”)
In some simple scenarios, minor failure can be caused by file in use or lack of permission issue and can be quickly resolved by 'Repair' option in 'setup.exe' program to repair the corrupted patch installation.
DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
