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:
CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [DatabaseMailUserRole]
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
Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.