SQL Server 2016 issue after installing KB5046063

Overview

After installing a recent SQL 2016 update (KB5046063) the SQL service was unable to start. With the error:

Wait on the Database Engine
recovery handle failed. Check the SQL Server error log for potential causes.

There was little information in windows event viewer. In the
SQL Server error log, there was the error message:

Error: 574, Severity: 16,
State: 0.

CONFIG statement cannot be
used inside a user transaction.

Error: 912, Severity: 21,
State: 2.

Script level upgrade for
database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’
encountered error 574, state 0, severity 16.

Cause and Resolution

This error occurs when SQL attempts to make changes to objects or permissions that are orphaned. In this instance this was found to be a SQL agent job that was owned by an orphaned user.

This was found and resolved by uninstalling the SQL update through programs and features. This allowed for the instance to be started, the permissions issue found and resolved. Once the permissions were amended the upgrade was installed and the SQL service started without issues.

Further Information

Microsoft provides a resolution for this issue that does not require the upgrade to be uninstalled by removing the trace flag 902 from the startup parameters allowing for the instance to be started. However, this should only be used to troubleshoot the issue as it prevents the pending upgrade script from being ran on startup.

Microsoft lists the potential causes of this error as well as scripts to identify orphaned users and jobs.

–          User options cause transactions to fail.

–          Orphaned users cause transactions to fail.

–          Orphaned jobs cause transactions to fail.

When resolving the issue in this case the script provided by Microsoft did not return the job that was the cause of the issue

SELECT sj.name AS Job_Name,
    sl.name AS Job_Owner
FROM msdb.dbo.sysjobs_view sj
LEFT JOIN master.dbo.syslogins sl ON sj.owner_sid = sl.sid
WHERE sl.name <> 'sa'
ORDER BY sj.name


Copy

It was necessary to remove the where clause as the name of the job owner was returning as null and identify the job from the complete list.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-574

‍