Recently I was helping a client where SQL Server upgrade was failing with an error. If you have seen my blogs, there have been many blogs where I explain steps to fix upgrade-related issues. In this blog, we would learn about fixing another cause of script level upgrade for database ‘master’ failed.
Similar to earlier blogs of upgrade failures, here are the messages seen in the ERRORLOG. If you are new to SQL and don’t know about SQL Server ERRORLOG then please refer below blog. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
Here are the messages:
Error: 15151, Severity: 16, State: 1.
Cannot find the user ‘##MS_SSISServerCleanupJobUser##’, because it does not exist or you do not have permission.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 15151, state 1, 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. 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.
From the first message, it is clear that user “##MS_SSISServerCleanupJobUser##” is missing from SSISDB database. Due to this, there is some action in upgrade script of SQL Server upgrade is failing and hence the error. As you can assume, the solution would be to create the user in the database. But keep in mind, SQL is not getting started and hence it is important to know the trick to start SQL without this error first. Read further to understand the trick.
Whenever we have such upgrade script failure issue and SQL is not getting started, we need to use trace flag 902 to start SQL which would bypass script upgrade mode. This would allow us the find the cause and fix it. So, here are the steps I have done to fix missing user “##MS_SSISServerCleanupJobUser##”
- As I mentioned earlier, first we started SQL with trace flag 902. I started SQL using trace flag 902 as below via command prompt.
NET START MSSQLSERVER /T902
For named instance, we need to use below (replace instance name based on your environment)
NET START MSSQL$INSTANCENAME /T902
- As soon as SQL was started, I was able to connect because the upgrade script didn’t run. Here is the T-SQL which I ran to create the user which was missing.
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo] GO
- After creating the login, I stopped SQL Service using SQL Server Configuration Manager. We can also do it via command prompt using below command. Below is for the default instance.
NET STOP MSSQLSERVER
If you are dealing with named instance, then below is the command ((replace InstanceName based on your environment)
NET START MSSQL$INSTANCENAME
- Then start SQL normally (without trace flag) using SQL Server Configuration Manager.
And the issue was resolved. Have you faced any such interesting issue during SQL upgrade?
Reference : Pinal Dave (https://blog.sqlauthority.com)