SQL Server troubleshooting topics are always in high demand, and today’s blog post is inspired by one such error message. In a previous blog post, we discussed the upgrade script mode of SQL Server and how to handle the “Login Failed For User – Reason Server is in Script Upgrade Mode” error. Today, we will delve into another set of error messages that can cause serious issues with your SQL Server instance.

One of our readers reached out to us with the following error messages in their ERRORLOG:

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 'sqlagent100_msdb_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.
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.

These error messages can be quite alarming, especially the suggestion to restore the master database from a full backup or rebuild it. To investigate the cause of these errors, we asked our reader to share the script ‘sqlagent100_msdb_upgrade.sql’ that was mentioned in the error message.

Upon examining the script, we found a piece of code that was failing:

IF (EXISTS ( SELECT * FROM msdb.dbo.sysusers WHERE ( name = N'TargetServersRole' ) AND ( issqlrole = 1 )))
BEGIN
    -- If there are no members in the role, then drop and re-create it
    IF (( SELECT COUNT (*) FROM msdb.dbo.sysusers   su , msdb.dbo.sysmembers sm WHERE ( su.uid = sm.groupuid ) AND ( su.name = N'TargetServersRole' ) AND ( su.issqlrole = 1 )) = 0 )
    BEGIN
        EXECUTE msdb.dbo. sp_droprole @rolename = N'TargetServersRole'
        EXECUTE msdb.dbo. sp_addrole @rolename = N'TargetServersRole'
    END
END
ELSE
    EXECUTE msdb.dbo. sp_addrole @rolename = N'TargetServersRole'

In this case, the error was occurring while adding the ‘TargetServersRole’ role in the MSDB database. To resolve this issue, we provided the following steps:

1. Apply the trace flag -T902 on SQL Server Configuration Manager.
2. Start the SQL Services. The trace flag will bypass the upgrade script mode and allow you to connect to the SQL Server instance.
3. Find the schema/role ‘TargetServersRole’ in the MSDB database and make a note of its members, if any.
4. In our reader’s case, the schema had a different owner. We asked them to modify it using the following T-SQL:

USE [msdb]
GO
ALTER AUTHORIZATION ON ROLE::[TargetServersRole] TO [TargetServersRole]
GO

If you have encountered a similar issue, we hope that these steps will help you resolve it. Remember to always examine the errorlog entries for clues and take appropriate corrective actions. SQL Server troubleshooting can be challenging, but with the right approach, you can overcome any obstacles that come your way.