Script level upgrade for master database failed

  • Article

 

In this post I would like to explain one of the interesting issues that I encountered while upgrading a SQL Server Instance.

Symptoms

· SQL Server instance is upgraded using a service pack or any other update.

· After the upgrade SQL server service starts but it stops in next few seconds.

· When we verify the SQL Errorlog we get the below errors:

2014-11-19 22:06:47.63 spid7s Creating sp_ExternalMailQueueListener
2014-11-19 22:06:47.64 spid7s Creating sp_sysmail_activate
2014-11-19 22:06:47.66 spid7s Error: 15138, Severity: 16, State: 1.
2014-11-19 22:06:47.66 spid7s The database principal owns a schema in the database, and cannot be dropped.
2014-11-19 22:06:47.66 spid7s Error: 912, Severity: 21, State: 2.
2014-11-19 22:06:47.66 spid7s 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.
2014-11-19 22:06:47.66 spid7s Error: 3417, Severity: 21, State: 3.
2014-11-19 22:06:47.66 spid7s 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.
2014-11-19 22:06:47.66 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Note You may receive the following error message when you connect to the instance of SQL Server 2008 R2 in SQL Server Management Studio when the SQL is performing the database upgrade:

Error: 18401
Login failed for user ' <login name> '. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

 

Cause

· The SQL server is trying to perform the database upgrade by executing the 'sqlagent100_msdb_upgrade.sql'

· This script can be found at  “C:\Program Files\Microsoft SQL Server\MSSQLxx.yyyy\MSSQL\Install”

 

Where xx stands for SQL version

SQL Server 2008 –> 10

SQL Server 2008 R2 –> 10_50

And yyyy stands for Instance_ID

· The script try to drop and recreate few roles and objects and it fails which dropping the role “ 'DatabaseMailUserRole' ”

   Script Snippet

:
  --------------------------------------------------------------
-- Database Mail roles and permissions
--------------------------------------------------------------
-- Create the DatabaseMailUserRole role
IF (EXISTS (SELECT *
FROM msdb.dbo.sysusers
WHERE (name = N'DatabaseMailUserRole')
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'DatabaseMailUserRole')
AND (su.issqlrole = 1)) = 0)
BEGIN
EXECUTE msdb.dbo.sp_droprole @rolename = N'DatabaseMailUserRole' ****************>> Point of failure
EXECUTE msdb.dbo.sp_addrole @rolename = N'DatabaseMailUserRole'
END
END
ELSE
EXECUTE msdb.dbo.sp_addrole @rolename = N'DatabaseMailUserRole' 

The above query fails as role "DatabaseMailUserRole" owns a custom(user-created) schema.

 

Resolution

  • Started the SQL with traceflag -T902

Traceflag 902 will skip the database upgrade process which allows the users to login.

cmd

C:\Windows\system32> net start mssql$SQL2008R2 /T902

The SQL Server (SQL2008R2) service is starting..

The SQL Server (SQL2008R2) service was started successfully.

  • Check the consistency of the MSDB database and take a full database backup.

DBCC CHECKDB (MSDB)
GO

BACKUP DATABASE [msdb] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\msdb.bak'
WITH NOFORMAT, NOINIT, NAME = N'msdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1

GO

  • Verify if the role “'DatabaseMailUserRole'” owns any user schemas

--To find the list of schema owned by database role 'DatabaseMailUserRole'

select sch.name as [Schema-Name], dbpri.name as [Schema-Owner]
from sys.schemas as sch
inner join sys.database_principals dbpri on dbpri.principal_id = sch.principal_id
where sch.name like 'DatabaseMailUserRole'
or dbpri.name like 'DatabaseMailUserRole'

Sample output:

------------------------------------
Schema-Name Schema-Owner
------------------------------------

DatabaseMailUserRole DatabaseMailUserRole
A_Custom_Schema DatabaseMailUserRole

(2 row(s) affected)

  • Create a test role and make it the owner of the custom schema which was owned by “DatabaseMailUserRole”

USE [msdb]
GO

CREATE ROLE [A_Test_Role]
GO

ALTER AUTHORIZATION ON SCHEMA::[A_Custom_Schema] TO [A_Test_Role]
GO

  • After the above modification start the SQL Service normally without any additional trace flag. This time the SQL completes the database upgrade successfully.
  • Revert the change which we made if the application requires it.

ALTER AUTHORIZATION ON SCHEMA::[A_Custom_Schema] TO [DatabaseMailUserRole]
GO
DROP ROLE [A_Test_Role]
GO

Written by:

Raghavendra Srinivasan, Support Engineer, SQL Server Support

Reviewed by:

Vijay Rodrigues, Support Escalation Engineer, SQL Server Support

Technorati Tags: sql,sql server,script upgrade