SQL Server troubleshooting topics are always in high demand, and today’s blog post is inspired by one such error message. In a previous article, we discussed the upgrade script mode of SQL Server and how to handle the “Login Failed For User” error when the server is in script upgrade mode.

Recently, a reader reached out to me with a different error message in the ERRORLOG file:

spid9s Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 2714, state 6, severity 25.

This error is a serious condition that can interfere with regular database operations and may even take the database offline. If this error occurs during the upgrade of the ‘master’ database, it can prevent the entire SQL Server instance from starting.

Upon examining the previous errorlog entries, we found the following interesting errors:

There is already an object named ‘DatabaseMailUserRole’ in the database.

CREATE SCHEMA failed due to previous errors.

The error message indicates that there is an issue with the ‘DatabaseMailUserRole’ schema. However, when we checked the system databases in SQL Server Management Studio (SSMS), we couldn’t find the ‘DatabaseMailUserRole’ under the msdb database’s Security > Roles > Database Roles section.

To further investigate, we ran the following query:

SELECT * FROM msdb.dbo.sysusers WHERE (name = N'DatabaseMailUserRole')

Surprisingly, no results were returned, indicating that the ‘DatabaseMailUserRole’ didn’t exist in the MSDB. However, when we checked the schemas using the following query:

SELECT * FROM sys.schemas

We found that the ‘DatabaseMailUserRole’ schema did exist, but it was orphaned as no user was associated with it.

To resolve this issue, we had two options:

1. Drop the ‘DatabaseMailUserRole’ schema.
2. Create the ‘DatabaseMailUserRole’ user and make it the owner of the ‘DatabaseMailUserRole’ schema.

We chose the second option and used the following script to fix the orphaned schema:


USE [msdb]
GO
CREATE ROLE [DatabaseMailUserRole] AUTHORIZATION [dbo]
GO
USE [msdb]
GO
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [DatabaseMailUserRole]
GO


But what if SQL Server is not starting? In such cases, you can use trace flag 902 to start SQL Server. Here is an example of how to start SQL Server with trace flag 902:

NET START MSSQL$SQL2014 /T902

Have you encountered script upgrade failures in SQL Server? How did you resolve them? Let us know in the comments below!