SQL Server upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25
Upgrading SQL server to SP4 or SQL Server 2016 encountered error 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25
Whilst performing a dummy run for a SQL Server instance upgrade, I encountered this error and it resulted in the SQL service not starting after the upgrade and the upgrade wizard reported errors.
After a couple of attempts I had to dig into it to find what was going on so referencing the article SQL Server Service fails to start after applying patch. Error: CREATE SCHEMA failed due to previous errors I tried deleting the DatabaseMailUserRole Schema from msdb but the server still failed to start.
This was SQL Server 2012 so I checked the SQL server logs found in at
C:\Program Files\Microsoft SQL Server\MSSQL10\_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.txt
(the above path may vary between versions).
The tail of the log looked like the following, if I had not already removed the DatabaseMailUserRole, then it may have reported that too:
019-02-15 17:04:20.53 spid4s Setting object permissions...
2019-02-15 17:04:20.63 spid4s Error: 2714, Severity: 16, State: 6.
2019-02-15 17:04:20.63 spid4s **There is already an object named 'TargetServersRole' in the database. **
2019-02-15 17:04:20.63 spid4s Error: 2759, Severity: 16, State: 0.
2019-02-15 17:04:20.63 spid4s CREATE SCHEMA failed due to previous errors.
2019-02-15 17:04:20.63 spid4s Error: 912, Severity: 21, State: 2.
2019-02-15 17:04:20.63 spid4s 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.
2019-02-15 17:04:20.63 spid4s Error: 3417, Severity: 21, State: 3.
2019-02-15 17:04:20.63 spid4s 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.
2019-02-15 17:04:20.63 spid4s SQL Server shutdown has been initiated
2019-02-15 17:04:20.63 spid4s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2019-02-15 17:04:20.80 spid14s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) \[ MSSQLSvc/\*\*\*\] for the SQL Server service.
2019-02-15 17:04:20.80 spid14s The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) \[ MSSQLSvc/\*\*\*:1433 \] for the SQL Server service.
Notice the line, "There is already an object named 'TargetServersRole' in the database.", so it seems I had the same problem as that in the referenced blog post but in my case with a different schema role.
Solution
After running the upgrade and it failing, I started SQL server with the trace flag 902. I did this using
net start mssqlserver /T902
from an elevated command prompt. This prevents the startup scripts running on startup.
Once started, then connected to the SQL server instance using SSMS and located the 'TargetServersRole' under the msdb database and right click, deleted it.
Then stopped SQL server and restarted it normally, without the trace flag, so it runs the 'msdb110_upgrade.sql' at startup again.
This time it started normally.😎
Problem sorted!
If this was helpful please comment as it motivates me to blog more!