SQL SERVER – Script Level Upgrade for Database ‘master’ Failed Because Upgrade Step ‘SSIS_hotfix_install.sql’ Encountered Error 15151

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.

SQL SERVER - Script Level Upgrade for Database 'master' Failed Because Upgrade Step 'SSIS_hotfix_install.sql' Encountered Error 15151 ssis-usr-err-01

WORKAROUND/SOLUTION

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##”

  1. 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

Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

  1. 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.

USE [SSISDB] GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo] GO

  1. 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

  1. 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)

, , , , ,
Previous Post
SQL SERVER – Error in Validation: Listener in Workgroup – Unable to determine if the computer exists in the domain ‘WORKGROUP’
Next Post
SQL SERVER – FIX: Install Error: A Network-Related or Instance-Specific Error Occurred While Establishing a Connection to SQL Server

Related Posts

4 Comments. Leave new

  • I received the same error message. However, I have a ##MS_SSISServerCleanupJobUser## user in the SSISDB without Login. There is also a login ##MS_SSISServerCleanupJobUser##, so the alter statement

    ALTER USER [##MS_SSISServerCleanupJobUser##] WITH LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo] GO

    DIsplays a syntax error.

    Reply
  • I got this running. I had a login for ##MS_SSISServerCleanupJobUser##, and NOT [##MS_SSISServerCleanupJobLogin##]. Deleted the invalid Login and ran your script again, successfully. Restarted MSSQLSERVER and all is well.

    Reply
  • I received this error recently when trying to upgrade a 2019 SQL server. This article did help me understand how to resolve the issue, but does anyone know why this login did not exist in the first place? I would expect that this login should be created when SSIS was installed but the login did not exist on my server.

    Reply

Leave a Reply

Menu