SQL Server Patching – server won’t start

At times, specific topics come together simultaneously. In this case, several emails were received from the Data Platform MVP distribution list, and various customer reports and suitable posts in other blogs were found on “how DatabaseMailUserRole stops your patching,” so a German contribution was made to this. It’s about a bug that has appeared in all SQL Server versions and editions in various constellations for several years. Most DBAs will never encounter this problem, and they will probably never think of this problem beforehand. If so, an explanation is provided below.

When does the error occur? – Starting position

The error occurs during the rollout of patches on Windows servers (including SQL Server Cumulative Updates), and the SQL Server database service is not restarted cleanly. Troubleshooting begins, and at some point, it is discovered that the SQL Server received the patch and then booted cleanly. However, the SQL Server database service was not restarted cleanly. The corresponding messages in the SQL Server error log are found, and it is observed that the SQL Server also changes 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 work on ALL (!) SQL servers for all customers in all forms are precisely where the problem is. There will always be one (or two) systems that deviate from the “standard” by “accident” or due to an administrative adjustment when the customer tells you:

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…

You will 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.

How to solve the issue with DatabaseMailUserRole?

Regarding “TargetServersRole,” it is sufficient to start the SQL service manually with the trace flag 902 and delete the schema in the “msdb => Security => Schema.” With “DatabaseMailUserRole,” it gets a little more complicated. Here, a little more T-SQL code has to be executed because this role cannot be found via the SQL Server Management Studio since this is an orphaned user. A good explanation and solution for this have been provided by Pinal Dave, which is as follows:

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

With the last correction, the error with the TargetServersRole first appeared. The SQL Server Engine restarted without a trace flag and again ran into an error situation, this time with the DatabaseMailUserRole. Therefore, this error/these errors can also occur one after the other. In both situations, customers were quickly cleaned up with the help of the blog posts, and the SQL Server was made to run again quickly.

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