Long story short Windows Update installed SQL Server Updates. To our surprise the MSSQLSSERVER service did not start. After looking at the logs I saw what the problem was (even though I had no idea how to fix it). Here is the copy of the section of the LOG that shows the error.
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: 3417, Severity: 21, State: 3.
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.
SQL Server shutdown has been initiated
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I have no idea what all that means, I am not a DBA. So I go to Google and start searching for this specific error. I bring in our DB/DEV team and they are as stumped as I am. This is a production server so we need it to be up to date with patches.
We for out some bucks and get some help. It was a simple fix but wanted to post the fix here in case any one else runs into this issue.
- Run the patch
- After running the patch, SQL will not start, we need to go to SQL Configuration Manager and add the TraceFlag -T902 as a startup parameter so it skips the upgrade script and we can start SQL.
- After applying the TraceFlag, restart SQL so it will use the TF.
- After SQL has started, open SQL Server Management Studio and run the following query in order
GO
==========================
CREATE ROLE [TargetServersRole] AUTHORIZATION [dbo]
GO
=========================
USE [msdb]
GO
=========================
ALTER AUTHORIZATION ON SCHEMA::[TargetServersRole] TO [TargetServersRole]
GO
After running the queries successfully, take the TF -T902 off of the startup parameters using SQL Configuration Manager
- Restart SQL server after taking off the TF.
- SQL will be slow to startup this time because it is going through the upgrade script, once it finishes it should startup successfully.
