SQL Server Service fails to start after applying patch. Error: CREATE SCHEMA failed due to previous errors.
In this post we would like to explain one of the interesting issues that we encountered while upgrading a SQL Server Instance.
Symptoms
SQL Server Service fails to start after applying SQL patch due to misconfiguration in MSDB.
Error:-
2016-06-28 19:23:41.22 spid5s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 2714, state 6, severity 25. Severity: 16, State: 0.
2016-06-28 19:23:41.22 spid5s CREATE SCHEMA failed due to previous errors. 2016-06-28 19:23:41.22 spid5s Error: 912, Severity: 21, State: 2.
2016-06-28 19:23:41.22 spid5s Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_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. Examine the previous error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-06-28 19:23:41.22 spid5s Error: 3417, Severity: 21, State: 3.
2016-06-28 19:23:41.22 spid5s 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.
2016-06-28 19:23:41.22 spid5s SQL Server shutdown has been initiated·
Cause
- The upgrade script [msdb110_upgrade.sql ] executes during the first restart of SQL after the service pack installation.
- This script hits an exception during recreation of database role called “DatabaseMailUserRole”.
- This is due to the fact that the schema named “DatabaseMailUserRole” was owned by other database role then “DatabaseMailUserRole” – DBO role in our case.
Resolution
1. Start the SQL server service using Trace flag -T902 ( 902 : Used to skip execution of any scripts during SQL Startup)
Or follow these steps:
· Open SQL Server Configuration Manager.
· In SQL Server Configuration Manager, click SQL Server Services.
· Double-click the SQL Server service.
· In the SQL Server Properties dialog box, click the Advanced tab.
· On click the Advanced tab, locate the Startup Parameters item.
· Add ;-T902 to the end of the existing string value, and then click OK.
· Restart the SQL server service
2. Connect to the SQL instance and backup the MSDB database
3. Manually delete the schema named “DatabaseMailUserRole”
Management studio > Expand MSDB Database > Go to Security > Schemas > Look for DatabaseMailuserRole
4. Now delete the schema named DatabaseMailuserRole.
5. Restart the SQL Server service.
More information
- Starting SQL Server 2008 onwards, whenever we upgrade or apply a patch on SQL, it upgrades only the binaries and not the database and its objects.
- Once the upgrade completes and the service restarts for the first time, it starts the database upgrade using script msdb110_upgrade.sql which is located under
C:\Program Files\Microsoft SQL Server\MSSQLXX.YYYY\MSSQL\Install\
XX : SQL Version
SQL 2008/R2 >10
SQL 2012 >11
SQL 2014 > 12
————————————————————–
— Database Mail roles and permissions
————————————————————–
— Create the DatabaseMailUserRole role
IF (EXISTS (SELECT *
FROM msdb.dbo.sysusers
WHERE (name = N’DatabaseMailUserRole’)
AND (issqlrole = 1)))
BEGIN — 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’
EXECUTE msdb.dbo.sp_addrole @rolename = N’DatabaseMailUserRole’ << **************Point of failure
END
END
ELSE
EXECUTE msdb.dbo.sp_addrole @rolename = N’DatabaseMailUserRole’
- The command “SP_ADDROLE” fails as it also creates schema named “DatabaseMailUserRole” which already exists in the MSDB database.
- The previous command “SP_DROPROLE” was unable to delete the schema “DatabaseMailUserRole” as it was owned by other database role– DBO role in our case.
Steps to repro the error:-
1. Change the schema owner of “DatabaseMailUserRole” to DBO
USE [msdb]
GO
ALTER AUTHORIZATION ON SCHEMA::[DatabaseMailUserRole] TO [dbo]
GO
2. Try to execute the below statement and we hit the same error:
EXECUTE msdb.dbo.sp_droprole @rolename = N’DatabaseMailUserRole’
EXECUTE msdb.dbo.sp_addrole @rolename = N’DatabaseMailUserRole’ <<<**************Point of failure
Related articles:
- Using the SQL Server Service Startup Options https://msdn.microsoft.com/en-us/library/ms190737(v=sql.105).aspx
- How to: Configure Server Startup Options (SQL Server Configuration Manager) https://msdn.microsoft.com/en-us/library/ms345416(v=sql.105).aspx
- ALTER AUTHORIZATION (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms187359.aspx
- sp_addrole (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms189743.aspx