Error on 2008 and 2012: Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’

Error on SQL 2008: Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, 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. (solved via the method given in the workaround tab)

Error on SQL 2012: Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16 (see number 3 below for resolution)

 

Issue: SQL Server 2008/2012 instance fails to start or hangs after service pack or Cumulative update installation and from eventviewer find out that “SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf” due to invalid default database locations.

–SQL Server not able to create temp_MS_AgentSigningCertificate_database.mdf

Error:Directory lookup for the file “D:\MSSQL10.I1\temp_MS_AgentSigningCertificate_database.mdf” failed with the operating system error 2(The system cannot find the file specified.).

Error: 1802, Severity: 16, State: 1.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

spid7s Error: 912, Severity: 21, State: 2.
spid7s Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 598, state 1, severity 25.

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘E:\MSSQL10.I1\temp_MS_AgentSigningCertificate_database_log.LDF’.

 

Workaround: 

This error is raised when the default database location is invalid. DUring the service pack upgrade or CU updates, tempdb database creates this certificates at the default db location. If that location is invalid it through this dangerous error. Edit below registry to have a valid directory for default database location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<Instance Name>\Setup\SQLDataRoot

 

Other workarounds (if above one didn’t solve your issue):

This error was caused by this reason to me. But if that didn’t solve your problems you could look for more options as below as recommended by others on google.

 

1. Start SQL Server from command prompt using trace flag -T902 to disable script execution.

–Turn off Implicit transaction
EXEC sys.sp_configure N’user options’, N’0′
GO
RECONFIGURE WITH OVERRIDE
GO

2. Find and fix if there’s any orphan users in system databases.

select ‘exec ‘+sd.name+’..sp_change_users_login ”report”;’
from master..sysdatabases sd
where sd.name in (‘master’,’model’,’msdb’)

3. If you see this error: Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 15173, state 1, severity 16

–Revoke the permissions granted on ‘##MS_PolicyEventProcessingLogin##’
–you can use the below script to identify the users who have permissions granted on ‘##MS_PolicyEventProcessingLogin##’

select a.name,b.permission_name from sys.server_principals a,sys.server_permissions b,sys.server_principals c
where a.principal_id= b.grantee_principal_id and b.grantor_principal_id=c.principal_id and c.name = ‘##MS_PolicyEventProcessingLogin##’

 

Good Luck !