Script level upgrade for database ‘master’ failed after Applying SQL 2008 R2 SP2

I worked on a issue where SQL Server 2008 R2 instance failed to start after installing service pack 2. SP2 installation was successful, but SQL Services fail to come online. Checked from services.msc and found SQL Services are in stopped state and SQL cluster resources were in offline state.

Checked SQL errorlog and found below error caused the failure

spid8s Creating procedure [dbo].[sp_syspolicy_purge_health_state] ...
spid8s Error: 15138, Severity: 16, State: 1.
spid8s The database principal owns a schema in the database, and cannot be dropped.
spid8s Error: 912, Severity: 21, State: 2.
spid8s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15138, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be tak
spid8s Error: 3417, Severity: 21, State: 3.
spid8s 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 “The database principal owns a schema in the database, and cannot be dropped” will occur if we try to drop a user that owns a schema. In this case, script upgrade is running scripts from file sqlagent100_msdb_upgrade.sql where it was trying to drop a user. Reviewed the script “sqlagent100_msdb_upgrade.sql” which is found in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install”

From the script, found below code where it was trying to drop user ##MS_PolicyEventProcessingLogin## and later recreating it.

IF EXISTS (SELECT * from sys.database_principals where name = N'##MS_PolicyEventProcessingLogin##')
DROP USER [##MS_PolicyEventProcessingLogin##]
GO
use master
GO
IF EXISTS (SELECT * from sys.server_principals WHERE name = '##MS_PolicyEventProcessingLogin##')
BEGIN
IF EXISTS (SELECT * from sys.server_triggers WHERE name = N'syspolicy_server_trigger')
DROP TRIGGER [syspolicy_server_trigger] ON ALL SERVER
DROP LOGIN [##MS_PolicyEventProcessingLogin##]
END

SQL was unable to drop the user “MS_PolicyEventProcessingLogin” because this user was owning schema “db_owner” in MSDB database. To allow dropping this user we need to transfer the ownership to another user.

First, to run any SQL commands, we need to establish the connection to SQL Server, but in this case the SQL instance is in stopped state.

Using below steps started SQL Server using trace flag -T902 where it skips running the upgrade scripts.
SQL Server Configuration Manager can be used to set the SQL Server start-up parameters. You can specify the trace flag there:

Start -> All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

In SQL Server Configuration Manager, click SQL Server Services.

In the right pane, right-click SQL Server (), and then click Properties.

On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter (in this case the trace flag -T902), and then click Add. You will now see the parameters similar to below

SQL Server Istance Startup Parameters

SQL Server Istance Startup Parameters

Click OK.
Restart the Database Engine.

Ran below command to transfer the ownership of schema db_owner to user dbo
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo

Removed trace flag -T902 and started SQL Server normally and this time the SQL Server completed running the upgrade scripts successfully.

Tested the failover and failback and everything worked well.

Cheers,
Keerthi Deep

 

2 comments

  • Pingback: Starting SQL Server using -T902 Trace Flag to skip the Script Upgrade | SQLServerF1

  • Thanks very much for blogging about this. We already had SQL2008R2 SP2, but in my case a security update failed KB3045313. I had the same:

    The database principal owns a schema in the database, and cannot be dropped.
    Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 15138, 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.

    However in my case rather than [##MS_PolicyEventProcessingLogin##] owning the “db_owner” schema, it owned its own schema of [##MS_PolicyEventProcessingLogin##]. You could only see this by running SELECT * FROM sys.schemas and not via SSMS. I checked that no objects were aligned to the schema, then dropped it. Restarted without the trace flag, and all is right with the world.

     

Leave a Reply

Your email address will not be published. Required fields are marked *