MSSQL error: "Script level upgrade for database 'master' failed ... upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25."

Question

All of a sudden one day (on my DEV PC) my Microsoft SQL Server 2012 instance (installed as instance name "SQL2012") would not start (all my other installed instances did). Trying to start it manually under Services failed. I don't recall making any recent changes prior to this. The cause of the failure was a mystery.

On inspecting Event Viewer, under System it showed a rather amusing error message [emphasis mine]:

The SQL Server (SQL2012) service terminated with the following service-specific error:
WARNING: You have until SQL Server (SQL2012) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.

checking under Application Event Log, I found these 2 error messages (preceded by a number of MSSQL$SQL2012 informational messages):

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. 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.

followed by:

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.

Fearing having lost my system databases (and not having a backup of them to restore - who makes backups of their system dbs anyway??) and needing to access the instance, and attached databases - I was willing to try anything. Even the possible restore of the system databases: Restoring the SQL Server Master Database Even Without a Backup - but that looked quite complex.

Answer 1

The problem is the msdb_110.sql update script, the script is a bit of a mess, with mixed tabs and spaces (wtf?).

It tries to run a couple of procedures that fail, on startup of sql-server. They fail when the code-page is 65001 (usually because the BETA utf-8 code page option has been selected) and so SQL server fails to start.

This appears to happen any time a SQL Server update is installed. I only experience this error with SQL Server 2017, not 2019

Why?

Don't know? The script is a mess.

Solution

1. Deselect the use utd-8 code page option
2. Restart the machine
3. Start sql server and let it run the script
4. (optional) reselect the use utd-8 code page option
5. Restart machine again and sql server
6. (optinal but recommended) uninstall windows, install a unix and run postgres

Answer 2

I got the same problem SQL2017 after update Windows Patch Hotfix3391(KB5001228) after restart server MSSQL Fail to start and event viewer shown error below

Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. 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.

Solution Fix by remove Beta:Use Unicode UTF-8 for Worldwide lang.. in the Region Settings Then it require restart server. After restart MSSQL can start as normal.

Answer 3

Fortunately, I was eventually able to start the instance (thank you to this answer: https://stackoverflow.com/a/59676743/4993856 which I trusted, because Pinal Dave also mentions that particular switch in: SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step msdb110_upgrade.sql encountered error 926, state 1, severity 25) if I ran:

net start mssqlserver$SQL2012 /T902

This pointed to some issue with the upgrade script... (Remember SQL is installed with instance name: SQL2012, hence the mssqlserver$SQL2012 used above for the named instance).

After some more searching I discovered this post: Installing service pack / cumulative update on SQL Server 2016 / 2017 breaks database engine (not exactly the same SQL version as mine) which pointed to the following possible Region Settings setting (Control Panel [when viewed by 'icons'] > All Control Panel Items > Region > Administrative > "Change system locale..."):

"Beta: Use Unicode UTF-8 for worldwide language support" in Region Settings

THAT WAS IT!!! After de-selecting that option (and possibly restarting my computer), the MSSQL Server 2012 Instance started up without any issue, and I was able to access all my previously attached databases.

I assume the pending upgrade scripts ran successfully. Thinking back about it now, it is possible that I agreed to installing a SQL Update, and never bothered to test access to the instance afterwards.

I also don't recall exactly why I chose to enable that specific setting under Region Settings, possibly due to some Linux compatibility, but it looks like it has become defaulted 'on' in recent Windows builds.