SQL Server 2012 CU1 upgrade step ‘msdb110_upgrade.sql’ encountered error 547

Recently I ran into an interesting problem that I thought I would share with you. I had just installed a new production SQL Server 2012 Enterprise Edition cluster, and decided to apply CU1. I did this in the usual manner, passive node first. The installation on the passive node went fine, as expected. However, when I failed the instance over to the other node, SQL Server failed to start. Failing back gave the same result. The errors I found in the SQL Server Errorlogs where not very encouraging:

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.

Error: 3417, Severity: 21, State: 3.

Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 547, state 0, severity 16. 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

Error: 912, Severity: 21, State: 2.

The DELETE statement conflicted with the REFERENCE constraint “FK__syspolicy__polic__627A95E8”. The conflict occurred in database “msdb”, table “dbo.syspolicy_policy_execution_history_internal”, column ‘policy_id’.

Error: 547, Severity: 16, State: 0.

It seemed that due to the fact that I had evaluated some policies, and generated some policy history the upgrade script could not delete the policies before creating them again with the new version.

So, what to do?

As SQL Server refuses to start, just logging in and deleting the rows are out of the question.

First, I located the ‘msdb110_upgrade.sql’ script on the HDD, and tried to modify it to first delete the history rows, and the update the policies. However, that did not work, as SQL Server seems to not use the scripts on the HDD when starting up but is storing them internally.

Secondly, as it took SQL Server about a minute from starting up, until it failed, I tried to quickly log in during that time, to delete the rows. That did not work either, as SQL Server was in the special upgrade mode, and would not let me in, not even on the DAC.

The third thing to try was to see if I could get SQL Server to skip the script by applying Trace flag 4022 to bypass Startup Prcedures, but no such luck.

In the end, it was Trace flag 902 that saved the day, and what it does is exactly to bypass upgrade scripts. After applying the Trace flag, restarting SQL, deleting the offending rows, removing the Trace Flag and restarting SQL again, all was well!

I have filed an Connect item about this at https://connect.microsoft.com/SQLServer/feedback/details/738612/sql-server-2012-cu1-upgrade-step-msdb110-upgrade-sql-encountered-error-547 and you should be able to follow what happens to this issue there!