SQL Server Patching - server won't start

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Sometimes certain topics come together in a timely manner, in this case, there were several emails from the Data Platform MVP distribution list, several customer reports and also suitable posts in other blogs, so I would like to make a German contribution to this. 😉
It’s about a bug that seems to have been appearing in all SQL Server versions and editions in various constellations for several years. Most DBAs will very likely never encounter this problem, and probably never think of this problem beforehand. If so, here’s an explanation…

When does the error occur? – Starting position
Who does not know it? You initiate the rollout of patches on your Windows servers (including SQL Server Cumulative Updates) and wait for the finished message…. After a while, the WSUS console reports and reports that all patches have been rolled out “successfully”… the departments involved are reported that the rollout process has been successfully completed… but then production comes and reports that production is not restarting…

Or a customer answers with the words:

By the way, we are currently importing a backup because after the Windows update (incl. SQL Server updates) the service for the server (and a few other services) no longer starts…

Now the troubleshooting begins and at some point, you find out that the SQL Server has received the patch and then booted cleanly, but the SQL Server database service was not restarted cleanly… continue troubleshooting and then in the Find the corresponding messages in the SQL Server error log:

Error: 2714, Severity: 16, State: 6.
There is already an object named 'TargetServersRole' in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, 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.

Error: 2714, Severity: 16, State: 25.
There is already an object named 'DatabaseMailUserRole' in the database.
Error: 2759, Severity: 16, State: 0.
CREATE SCHEMA failed due to previous errors.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, 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.

All DBAs should be aware that the SQL Server also makes changes to the database structures of the system databases as part of a patch; these changes are usually carried out using T-SQL statements via an SQL file. These scripts should actually work on ALL (!) SQL servers for all customers in all forms, this is exactly where the problem is… there will always be one (or two) systems that deviate from the “standard”… be it ” by accident” or due to an administrative adjustment.

Related to “TargetServersRole” – here it is sufficient to start the SQL service manually with the trace flag 902 and to delete the schema in the “msdb => Security => Schema”.

With “DatabaseMailUserRole” it gets a little more difficult, a little more T-SQL code has to be executed here, because this role cannot be found via the SQL Server Management Studio, since this is an oprhaned user. Pinal Dave has written a good explanation and solution for this:

USE [msdb]
GO
CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]
GO
USE [msdb]
GO
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [DatabaseMailUserRole]
GO

With my last correction, the error with the TargetServersRole first appeared, then the SQL Server Engine restarted without a trace flag and ran into an error situation again, this time with the DatabaseMailUserRole, so this error/these errors can also occur one after the other. In both situations, I was able to clean up my customers quite quickly with the help of the blog posts and make the SQL Server running again within a very short time.

Thanks to Tim Wappat for solving and cleaning up the TargetServersRole
https://timwappat.info/post/2019/02/15/SQLUpgradeFailMsdb110_upgradesql (can’t be embedded so nicely 🙁 )

Thanks to Pinal Dave for solving and cleaning up the DatabaseMailUserRole

SQL SERVER – Script level upgrade for database ‘master’ failed – There is already an object named ‘DatabaseMailUserRole’ in the database

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Björn Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.

Amazon.com Empfehlungen

Damit ich auch meine Kosten für den Blog ein wenig senken kann, verwende ich auf diese Seite das Amazon.com Affiliate Programm, so bekomme ich - falls ihr ein Produkt über meinen Link kauft, eine kleine Provision (ohne zusätzliche Kosten für euch!).